Frequency Dist of Time Range

[m17347047711116]

Working on a Shift Schedule in access
have a few box's

[Name] [Date] [Start Time] & [Finish Time]

Would like to calculate the number of people on shift at a given time.

ie
Employee 1 starts at 7:30 am and Finishes at 9:00 am
Employee 2 Srarts at 8:00 am and Finishes at 9:30 am

The frequency analysis should show that there are
7:30----(1)
8:00----(2)
8:30----(2)
9:00----(1)

I am working towards using this frequency to link in excel to build a time graph that shows Staff requirements. hope i am heading in the right direction.

Any help would be appreciated

 

[The_Doc_Man]

The problem you have is to define how many folks are there at the given time. This is kind of like a Riemann Sum problem.

The method I might use is, if everyone has to check in to the closest half-hour, I would define a separate "driver" table with the times of interest for the day. The fields might be as simple as

tblDriver
-- Time
-- StaffCount

Then I would write a query to update the driver table. In the query design grid, I would store the count of records in your checkin/checkout table where the driver table's time is between the other table's In and Out times (inclusive of the ends, perhaps)

There are implications of correctly including the right date as part of the query, so you might need to consider various applications of the DatePart function to help drive the times. Then, once that table was updated, you could plot the driver table directly.

OK, purists out there, I know you could do this in a query, but some people prefer to do this in separate stages so they can look at their data as a sanity check.

Now, m, the purist's version of the query COULD be used for the graph you describe. In which case your query might be a two-column query consisting of times from tblDriver and the DCount of the other table using that BETWEEN criterion I mentioned earlier.

Unfortunately, you cannot do a JOIN between these two tables. This is because of the possibility that at a particular time, neither the In time nor the Out time will match the driver-table's time, yet you should still count the record.