I am playing with a new project this week and I am trying to formulate in my head how I would go about doing this.
I want to create a database for tracking employees hours of work. I would like to have a form where I choose an employee's name from a combobox and select a date, then have a subform (in datasheet view) where I enter the employee's time record and have it calculate the hours based on a set of rules. This seems like quite a simple thing but I have a feeling it is more complicated than I might suspect. Here is a picture of a timesheet to show you what I am trying to accomplish.
The first column (Number (PK)) would not actually show in the in the subform and the Hours X1, Hours X1.5, and Hours X2 columns would auto-calculate based on the clock records entered in the Start Time and End Time fields.
As you can see the hours calculated for the first clock records entered actually include the hours from 11:00 to 14:15, not 11:00 to 13:45. This is because the time between the End Time of the 1st record and the Start Time of the 2nd record is only 0.5 hours and is therefore paid time. However, the time between the End time of the 3rd record and the Start Time of the 4th record is 1 hour and this is therefore unpaid time.
I could obviously tell the database how long the breaks are and then have it calculate accordingly. However all of the information required to calculate the break (and make decisions appropriately) is available to the database if I can figure out how to get it to compare one record to another.
This is the question I have. Is there a way (with VBA code) to compare the End Time field of one record with The Start Time field of another (next) record and have it calculate the time difference (number of hours, i.e. 0.5 hours, 1 hour, or more). Then using this information I can make decisions about how the total hours are calculated (is the break paid or not)?
I hope this makes sense.
TIA for any help.
John