Work Periods

[Seb]

HI All

I have a question that I just cant seem to get my head around....so I thought I'd pick your brains!

I have a table that has DriverID, Date, Work, Sleep, Rest. As you can imagine, this records their hours of work, sleep & rest.

Now with new fatigue management laws in Oz, we need to find out if they're working for more than 168 hours in a 14 day period.....

My thoughts are, create a query that runs through every date of the year, then counts forward 2 weeks, sums the WORK hours and only displays anything over 168 hours??? Is this correct? Would that not simply die ? Considering we have about 30 drivers to run through?

 

[John_W]

I've used DSUM in a query to do something similar. It will work, but might run a bit slow.

 

[DCrake]

Simple Software Solutions

Hi

Cant grasp why you need to count through every day of the year then count forward 2 weeks?

However, here is an 'off the top of my head' solution.

Firstly create new query that contains the driver, worked hours and the date

Next create a new field in the query something like Prd:iif(Format(DateWorked,"d") < 16,Format(DateWorked,"mm") & "A",Format(DateWorked,"mm") & "B")

The above looks at the day of the month and if it is below 15 then call it the month number plus "A" else month number plus "B". This splits the records in half months - 2 week periods. unfortunately Access does not provide this date part.

Next Create a new query that is sourced from the above query

Group on Driver
Group on Prd
Sum Worked Hours

Enter a date range in the query be it over a month a quarter, year, whatever, upon the Date Worked column.

Sort the query by Worked Hours Descending By Period.
Run the Query

Sample Results

Driver Hours Period

Bill 200 10A
Tom 160 10A
Jack 148 10A
Bill 170 10B
Tom 160 10B
Jack 90 10B

So as you can see in the first 2 weeks of October (10A) Bill worked 200 hours

Hope you get the gist of this and you can manipulate it to suit your needs.

Code Master

 

[Seb]

Thats defenitely along the right lines, but the bloody local government here in Oz are extremely strict on this. It clearly says you cannot work for more than 168 hours for any 14 day period??? So 14 days from today, then 14 days from tomorrow, etc, etc

on another note: there's heaps of other things I cant monitor in the DB due to the customer only wanting to enter WORKl, SLEEP & REST hours. Normally, I would need to show that :

1. No more than 5 hours work without a 30 minute break
2. No less than 7 hours CONTINUOUS sleep (how do you measure that!!!)
3. Not more than 15 hours of work time in 24 hours period (not for the day, but in 24 hours)

its all crap really, but they need to follow it up???

 

[DCrake]

Simple Software Solutions

Seb

Its a case GIGO Garbage In Garbage Out or as I normally say SISO, I'll let you work that one out for yourself.

Take the senario what a driver goes to sleep at 10pm on a Monday and wakes up at 5am on a Tuesday. Does the driver book 2 hours for Monday and then 5 hours for Tuesday? or does he simply book 7 hours for Monday?

I can't see each driver being so diligent so therefore you would have to calculate the split time yourself. Another problem is that you are entering the data retrospectivly, so what has been has been. My second thoughts are to store the results in minutes as opposed to hours to enable you calculate it easier.

Also think about how you are capturing the data, is it done with automation or is a user entering the data manually via a form. If the latter then you could actually code the rules into your form as save flags as to the outcome of the input.

Code Master

 

[Seb]

Hi Drak

I agree SISO!! As for your question about the driver's sleeping....
The period is constantly rolling forward. So its not a matter of how many hours did you sleep/work/rest today but how many hours since your last sleep. If he has 2 sleep periods today (say 1am - 8am & 14pm - 21pm) his last sleep period ended at 9pm....so it goes from there....
Really complicated I know

As you said SISO. So I think I'll be getting the datacapturer to enter all the times (not just total SLEEP, REST, ACTIVE) so we can calculate it from there.

Not sure how I would do it, but I'll think of something

thanks for your input thus far though!! I'm sure you'll hear from me soon

 

[Seb]

I know its cheeky, but how would you suggest doing this DB? I've told the client they're gonna have to capture actual times (not sure Total times for the day)
Then how would I go through the criteria needed?

 

[The_Doc_Man]

Here's a thought. If it doesn't apply, well... I tried.

Based on the "Old Programmer" laws, the only way to get something out of a database is to put that something into it in the first place. Therefore... to be able to track dates for compliance, a date field has to occur somewhere for each entry that shows a driver's sleep and work and rest periods.

I'm going to assume that your DB is more or less normalized. So here is how I would do it if I had this problem.

tblDriver
DriverID, Primary Key, probably autonumber or employee number
Driver info like name, address, phone, whatever else you keep here

tblDriverItem
DriverID, Foreign Key
WorkItemDTM, date/time stamp of item
WorkItemHours, number of hours (or minutes)
WorkItemType, code for Work, Sleep, Rest
whatever else this entry must track.
(In this design, you make three short entries per date per driver.)

For this to work, do a query based on the tblDriverItem table rather than the tblDriver table. Use tblDriver only as a lookup source for driver data.

What you want is this: Group the table by DriverID, then order by the WorkItemDTM. (Or group by WorkItemDTM first and then order by Driver ID second).

Now make your query do DSums for each of the work categories where the criteria are that the WorkItemDTM in the DSUM must fall BETWEEN WorkItemDTM AND WorkItemDTM + 14 from the source record. This gives you a sliding window on the driver hours.

Grouping by driver ID first would give you Drivers, then Sliding windows for one driver. Grouping by driver ID last would give you the date windows and within each window you would see the drivers having entries that started at the start of that window.