Count between times

[Guest]

I am creating a database to track visits to an ER. I need to find a way to
count the number of visits in a given block of time.
ie. # of visits from 6:00 PM to 7:00PM. I know this shouldnt be as
difficult as I am making it, but I just cant seem to make it work.
The time is stored in a table with [admittime] as the field name.

 

[Jeff L]

Select Count(AdmitTime)

From YourTableName

Where AdmitTime Between #6:00:00 PM# And #7:00:00 PM#

 

[Guest]

That worked great for the time. Is there then a way to expand that so that
it gives me a count from each of the hour blocks? ie 6-7, 7-8, 8-9 etc.

 

[John Spencer]

Use the Hour function

SELECT Hour(AdmitTime) as HourNumber
, Count(AdmitTime) as VisitFrequency
FROM YourTable
GROUP BY Hour(AdmitTime)

If you want to limit this to specific date(s), you would need to include
that in a where clause.

Assumption: You have an AdmitDate field
Then for admissions on July 4th you would use something like

SELECT Hour(AdmitTime) as HourNumber
, Count(AdmitTime) as VisitFrequency
FROM YourTable
WHERE AdmitDate = #2006-07-04#
GROUP BY Hour(AdmitTime)