Count recrods per day to be used in graph

[shuttlelaunch]

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?

 

[dhookom]

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

 

[shuttlelaunch]

Do I do this in a normal query or a crosstob query?

 

[dhookom]

This would be a regular GROUP BY query.

Have you created the table of dates?

If you need more assistance please respond with your table and field names.

Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

I've created the table and query, but I dont see where to put Group By the [TheDate] and count [TheDate].

 

[dhookom]

If you need more assistance please respond [red]with your table and field names[/red].

Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

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].

 

[dhookom]

Try this SQL:

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

 

[shuttlelaunch]

Thank You!! It worked great!