Counting days without explicitly storing the start date would require some kind of scheduled task to count them. This is not trivial to implement and not recommended.
I would suggest storing the date when opened and then doing a date diff between the start date and the current date to work out the number of days (including the appropriate rounding). I would suggest that it might be a good idea to also store the closing date.
Edit:
When the item is closed you have two options to work out/read the days opened in the future:
- You can store both the start date and the close date and do a datediff between them when you need to know the days opened.
- You can store the days open explicitly when you mark the item as closed.
I would recommend 1 as it is more concise.
Edit2:
To fully explain point 1. Basically you would use the datediff function and compare the start date to the current date, while specifying days (d) as the required unit.
In an SQL select query this would look something like:
select
datediff('d',table.StartDate,date()) as OpenDays,
table.Status
from table
The date() function returns the current date.
Microsoft Access Help on DateDiff
Try this
SELECT
tblClient.[Printed Name],
tblClient.[Address 1],
tblClient.[Address 2],
tblClient.City,
tblClient.State,
tblClient.Zip,
tblClient.Phone,
tblClient.[Email Address],
(select count(*)
from tblCurrent
where tblCurrent.[Date Ordered]>#6/27/2013#
and tblClient.[Client Code] = tblCurrent.Client
) AS Qty
FROM tblClient
ORDER BY Qty desc;
Best Answer
The quick and dirty way to show totals is to add a Totals row to any Datasheet View of a table or query. First open the Datasheet, then on the Home ribbon (i.e. toolbar tab) click the Totals button (with the sigma sign). A Total row with empty cells appears at the bottom of the data rows . Clicking on a cell reveals a drop-down list with options for various aggregate functions, include sum. See this documentation page.
A more advanced way to get totals is to create an aggregate query. Within the standard Query Design View, click the Totals button on the query Design ribbon. This enables and shows a Total row in the design grid. For each column of the query, Totals options can be selected including "Group By" and various aggregate functions. The same query can also be edited in the SQL view which will show a GROUP BY clause and various aggregate functions in the column SELECT list. See this documentation page for further details.
Asking questions here is fine, but especially for beginning topics as this, the web contains many tutorial pages with much more detail, just as I linked to. All I did was search for "Access sum fields" and "Access query total" to find those pages. I suggest doing those same searches so that you can see various other resources and tutorials.