TOIL Tracking

[algecan]

Hi All, I’m hoping you can help me.

I’ve got to incorporate an excel spreadsheet we currently use to record employee time into our existing database.

The way the spreadsheet works is that each day is obviously on its own row. Then the employee records their start time, finish time and when their lunch break started and finished. That’s easy enough to put into a database however this database needs to also track their TOIL. For example if the employee has to work 7:30 hours a day and on one particular day they work 8 hours, they have 30mins of TOIL. This is then tracked in the spreadsheet for each day showing -/+TOIL for that day and a running -/+ TOIL. This is the bit I am struggling with.

So far I have a table which holds the employee, the week commencing date and then Monday_Start, Monday_Finish, Monday_Break_Start, Monday_Break_Finish and so on. Obviously the daily total of TOIL is a fairly simple calculation, however does anyone have any suggesting on how to keep a running total of TOIL?

I have had a look at as many time templates as I can find but none seem to accommodate for TOIL.

TIA.

 

[CJ_London]

What I can say is you will almost certainly need to use a subquery to provide a running total (or use a report which has a running total option).

If you require a more detailed answer, then rather than describing your table, suggest you provide some example data since it is not clear how it is constructed - is Tuesday in the same record or a different row? (if it is then your table is not constructed properly) - what are your field types? date and time or just time? Is it typical 8-5 type day or are there shifts or random working over a 24 hour period? Can someone work over midnight so start is on one day and end is on the next.