Query to flag a field based on calculated data

[Cruisemate]

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?

[Allen Browne]

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?

[Cruisemate]

I put that calculated field in, and no data comes up.
The original table is set up with the timein and timeout fields set as
date/time medium format.
I set up the second table with the time records as you advised also set
up as medium format (I also tried with short format)
but I'm still not getting any data.

[Allen Browne]

Does your date/time field contain a date component, or only a time
component?

The format is not important, as Access stores the date/time values as
fractional numbers, where the integer part represnts the date, and the
fraction part the time of day.

--
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**********************@f14g2000cwb.googlegr oups.com...

I put that calculated field in, and no data comes up.
The original table is set up with the timein and timeout fields set as
date/time medium format.
I set up the second table with the time records as you advised also set
up as medium format (I also tried with short format)
but I'm still not getting any data.

[Cruisemate]

When I put that calculated field into a query, no data comes up.
I have all the time fields set to short time and have made both tables
(not joined) but still no data.

[Cruisemate]

The field has a date component, but that is not important to me for my
results. basically I just need to find out how many people were clocked
in during each hour of operation, date is unimportant. I tried the
above query again, and still no data.

[Allen Browne]

--
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**********************@f14g2000cwb.googlegr oups.com...

The field has a date component, but that is not important to me for my
results. basically I just need to find out how many people were clocked
in during each hour of operation, date is unimportant. I tried the
above query again, and still no data.

[Allen Browne]

If your field has a date component, it won't match with the table that has
just the time component, unless we strip the date out.

Try:
TimeValue([TimeIn])
and
TimeValue([TimeOut])
in those expressions.

--
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**********************@f14g2000cwb.googlegr oups.com...

The field has a date component, but that is not important to me for my
results. basically I just need to find out how many people were clocked
in during each hour of operation, date is unimportant. I tried the
above query again, and still no data.