I need to calculate Regular Hours and Overtime Hours over the course of a 7-day period. I have 3 tables - Employee, Time Card and Calendar. Employee and Calendar are the Parents, Time Card is the child.
- Parent Fields - Employee: Full Name, Employee ID #, Hourly Rate
- Parent Fields - Calendar: Date, Week #
- Child Fields: Time In, Time Out, Total Hours (Duration Calculation), Total Labor $
- For each week #, I need a running summary of the EE's total hours worked for the week - referenced as WTD below.
- For example, the week starts on a Sunday
- Sunday - the EE worked 8 hours; WTD: 8 hours
- Monday - 10 hours: WTD 18 hours
- Tuesday - 12 hours: WTD: 30 hours
- Wednesday - 8 hours: WTD 38 hours
- Thursday - 12 hours: WTD 50 hours
- Sunday to Wednesday, all hours would be calculated at the regular rate of pay and be considered regular hours
- On Thursday, the EE exceeds 40 hours, so I would need to calculate 2 regular hours, and the remaining 10 hours at an overtime rate of pay.
I am lost and could use some help on this one ....