Help on Counting Query

[kr]

I have an employee dbs that we use to keep track of all employee info. We
are beginning to use the dbs for more - to keep track of schedules and # of
employees on duty in an hour. I am stumped. I need help starting with the
tables and then i would to end up with rpt or crosstab similar to this:

Mon Tues Wed Thurs Fri Sat Sun
7am 8 9 10
8am 8
9am 8
10am
etc - this would give me the # of employees who are working in these
hours/days. I have tried many steps to do this - 2 tables one w/the hours
and the other tbl w/employee nm/id and start/end times. But I dont know how
to put it all together to get the results I need. Can you help, please?
PS - we have different shifts throughout the day - that is why the # of
employees during a particular hour can change.

 

[Duane Hookom]

I would create at least 3 tables:
tblDates (one record for every date in range needed)
============
TheDate date/time

tblTimes (one record for every hour of the day)
============
TheTime date/time

tblEmployeeWork
=============
EmployeeID
WorkDate
WorkTimeStart
WorkTimeEnd

You could then create a query with all three tables. Join the TheDate field
to the WorkDate field. Then set the criteria under the TheTime field to
Between WorkTimeStart and WorkTimeEnd

 

[kr]

okay. great. i will try that. then from there could i turn it into a
calculated field?
now, with the info we need - it won't be a dated rpt necessarily - just a
general overview - but it should work. i wil try.

 

[Duane Hookom]

Actually, from the tables I suggested, you should be able to create the
desired output using a crosstab query.

 

[kr]

oh - gotcha! Thanx for your help!

Actually, from the tables I suggested, you should be able to create the
desired output using a crosstab query.