Creating an export report for staff with multiple departments/pay rates
Ok, here's my problem:
I'm attempting to create a time clock dbase that relates to staff members. Staff can clock in to multiple departments that they are assigned to and may have variable pay rates based on the department they're working for. These were done by a join table: Staff -< Wages >- Dept, using appropriate primary/foreign keys.
Staff pay cycle varies by position and can work bi-weekly or monthly cycles. Overtime is calculated based on department schedule (either 8 hr or 10 hr shifts) and/or 40 hr work weeks. When a staff member starts a new cycle, a new pay cycle record is created, a new time card is created and the related punch data. The tables are related: Staff -< Cycle -< Time Card -< TC Data.
Everything is working correctly, but now I'm having trouble creating a report to export for the payroll company to process. When I generate the report, I need to show 1 line item for each employee showing the total worked in a specific department during the current pay period. Example:
Name Dept Hrs Worked Total Pay
John Doe Kitchen 30 300.00
John Doe Pantry 10 120.00
I'm guessing that the report needs to be based on TC Data, filtered by pay cycle and then sorted by employee and then department. Then I need to have a running total, by department for that employee of their wages earned and probably involves a GetSummary evaluation. Any suggestions would be greatly appreciated.