AnsweredAssumed Answered

Creating an export report for staff with multiple departments/pay rates

Question asked by EricBrown_1 on Oct 1, 2014
Latest reply on Oct 1, 2014 by EricBrown_1


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.