Calendar report Column totals

[Michael Noblet]

I have a calendar report that shows the duration a person
is in the ER based on entry and exit times. Below is
sample data for this question:
Patient TimeIn TimeOut
JDM 00:17 03:08
JBL 00:26 05:28
SSE 00:31 04:45
ARW 00:46 04:34
MFW 00:49 03:10

I need to total the number of patients durring each hour
so:
12am = 5
1am = 5
2am = 5
3am = 5
4am = 3
5am = 1
6am = 0

any ideas on how to total or count the # of patients per
hour?

 

[Duane Hookom]

I would create a table of time ranges
tblTimeRanges
StartTime EndTime
12:00 AM 1:00 AM
1:00 AM 2:00 AM
2:00 AM 3:00 AM
etc
Add this query to your report's record source and set the criteria under
TimeIn to <[EndTime] and under TimeOut to >=[StartTime]
Make this into a totals query and only display the StartTime column and a
count of Patient.