Count if time is between two times

[barnstar]

I have a database that keeps track of everybody's shift times.
I need to be able to count how many people were in for each half hour of the day. For example if a shift time is 08:00 - 18:00 I would have a count for each half hour between these times.
I have tried many things but cannot get anything to work properly.

Any help would be much appreciated.

Thanks in advance

 

[barnstar]

Thanks for your reply.

Sorry I didn't really explain myself very well.

I need the count to be broken down into half hour time slots.

Below is an example of what I need.

07:00 07:30 08:00 08:30 09:00
0 0 1 1 1

etc.

Hope someone can help
Thanks

 

[plog]

I can think of 2 inelegant ways to do this. What is the datatype of your time field? What is the table name that time field is in? What is the name of the time field?

Let me know the answers to those questions and I'll explain both methods, unless someone else posts a pretty way in the meantime.

 

[vbaInet]

07:00 07:30 08:00 08:30 09:00
0 0 1 1 1

I don't understand the second zero. Isn't that supposed to be 1?

 

[barnstar]

Thanks for the response.

The datatype of the time field is 'Date/Time' and the table name is 'tblShifts'.

The Time field names are 'StartTime' and 'EndTime'.

Hope you can help.

Thanks

 

[plog]

I know I said 2 ways, but I think this way would be simplest and get you exactly where you wanted to be. What I would do is create a calculated field for each timeframe (i.e. 7-7:30, 7:30-8...) you want. You would see if the records StartTime and EndTime make that shift fall in that timeframe and count that record if it did. Then you'd sum all the records up and get a total for every timeframe.

To do that you would expand on this code:

SELECT SUM(IIf((Hour([StartTime])*60+Minute([StartTime])<=420) And ((Hour([EndTime])*60+Minute([EndTime]))>=450),1,0)) AS 0700to0729, SUM(IIf((Hour([StartTime])*60+Minute([StartTime])<=450) And ((Hour([EndTime])*60+Minute([EndTime]))>=480),1,0)) AS 0730to0800 
FROM tblShifts;

If you paste that SQL into a query and run it you will see what I mean. Right now it just works for 7-7:30 and 7:30-8:00. If you open it up in design view you can copy the fields I have created to generate fields for the rest of your time slots and updating the constants on the right sign of the comparison signs (i.e. 420 and 450, 450 and 480) by 30 for each new field you create.

 

[barnstar]

Alright plog. Absolute genius.

Thank you very much.