8 hour work shift volume

[Dale]

Access 2000

My table has a date/time field that holds the complete
date and time. (ex: 1/1/2000 2:07:00 AM) It also has a
field for Primary Complaint. (limited picklist)

I need to report on the count of the complaints for each
8 hour shift (7a - 3p, 3p - 11p, 11p - 7a)

This information needs to be on the same report and
within a specified data range. I understand how to limit
the date range but how can I divide the information into
3 shifts?

Any & All Help Always Appreciated!
Dale

 

[Sam D]

Dale,

Using a query you first need to calculate a common value for each shift on
which to group (obviously this is the hard bit!). Date/times are really
stored as decimals where the whole number part represents a day and the
decimal part is the fraction of the day.

I'll assume your date field is called JobDate. If we define shift 1 to be
the 7am-3pm shift, shift 2 to be 3pm-11pm and shift 3 to be 11pm-7am.
Clearly shift 3 includes midnight to 7am of the next day.

To determine the day each shift starts just subtract 7/24 from JobDate and
just use the integer part...
ShiftDate: Int(JobDate-7/24)

Now the decimal part of JobDate-7/24 needs to be split into 3 equal parts to
get the shifts...
Shift: (([jobdate]-(7/24))-Int([jobdate]-(7/24)))*100000\33333+1

Group on both of the above and add a count...
Complaints:Count(JobDate)

Post back to the group if needed.

HTH
Sam
:[email protected]...