When was my Worker's Last Day Off? (1 Viewer)

[XelaIrodavlas]

Hi all,

Looking for some tips on a tricky query:

We have a "Clock" table, which shows the Time & Date Employees started and stopped working. Using this, I need to determine what was the most recent 'day off' that Employee had, i.e. if you worked a regular 9-5 your last day off would have been Sunday 20th October.*

I figure I need to:

1. Group By Employee Name, then come up with an Expression (or perhaps a custom function?) to look back through the records. But first,
2. I'll need to calculate the time difference in Clock Out/In's, to determine when they weren't working, and the length of those periods.

I can't figure this out Any ideas?

TIA,



*This is in order to comply with Working Time Regulations (UK) which state an Employee must take at least 24Hours off within a 7 day period (or 48 hours off within 14 days).

 

[Minty]

To make this work efficiently I would suggest you create a calendar table, ( Ideas for it here ) and then you can easily create a query to join your clock table to the available dates and find the last calendar date that doesn't have matching record and then count the days / hours they did work.

You can possibly do it without the Calendar table, but they make this type of date related querying a lot simpler.

 

[plog]

What's your table look like? Better yet, give us some sample data, provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what the correct answer should be for each employee in A.

Again, sample data.

 

[MarkK]

One approach, write a query like...

SELECT DateValue(YourDateTimeField) As DateWorked
FROM tClock
WHERE EmployeeID = 123
GROUP BY DateValue(YourDateTimeField)
ORDER BY DateValue(YourDateTimeField) DESC;

Now open a Recordset on that query, enumerate its rows, and the first date you find missing is their last day off.