Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count recrods per day to be used in graph

Status
Not open for further replies.

shuttlelaunch

Technical User
Sep 22, 2011
15
US
I have database that we use at an animal shelter. The day an animal arrives we record the day [gain date] and when it leaves [check out date].

I am trying to make a query that I can use to create a graph of number of animals per day over the year. How do I create a query to count the number of animals per day?
 
I would start by creating a table with every date in it [tblDates] and field [TheDate].

Then create a query with tblDates and your table. Set the criteria under TheDate to
Between [Gain Date] And [Check Out Date]
Group By the [TheDate] and count [TheDate].


Duane
Hook'D on Access
MS Access MVP
 
I've created the table and query, but I dont see where to put Group By the [TheDate] and count [TheDate].
 
The table with the dates is named tblDates, the field is [TheDate].

The table with the check in/out dates is named MasterDatabase, the check in date is [GAIN DATE] and the checkout date is [CHECK OUT DATE].
 
Try this SQL:
Code:
SELECT TheDate, Count(TheDate) as TheCount
FROM tblDates, MasterDatabase
WHERE TheDate Between [Gain Date] And Nz([Check Out Date],Date())
GROUP BY TheDate
ORDER BY TheDate;

Duane
Hook'D on Access
MS Access MVP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top