The basic answer to your question is that two events overlap if:
A starts before B ends, AND
B starts before A ends.
You need a table containing a record for each hour:
ID HourStart HourEnd
1 0:00:00 0:59:59
2 1:00:00 1:59:59
...
24 23:00:00 23:59:59
You can create a query containing both tables, with no join between them. In
the Field row, enter this calculated field:
ThisHour: ((TimeIn < HourEnd) AND (HourStart < TimeOut))
In the Criteria row, enter True
This will cause your record to repeat for every hour that it overlaps with.
You can then count the records for any hour.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Cruisemate" <cr********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
I have a table with numerous fields including timeIn and timeOut field.
I need to find out how many people were clocked in during each
operating hour. Can I run a query that will allow me to compare the
time and 'flag' a field (a new field for each hour) so that I can run a
count on the completed data?