I have a payroll base that tracks timesheets and commissionable transactions for our employees. It includes the following linked tables:
- Employees
- Timesheets
The Employees table includes information for name, address, email, etc. Additionally, there is a Straight Time (ST) Hourly Rate field. We have thousands of records in the Timesheets table from daily entries submitted by employees. The submissions simply consist of:
- Employee Name
- Start Date/Time
- End Date/Time
Formula fields calculate the number of hours worked. To calculate ST Gross Pay, a lookup field displays the ST Hourly Rate from the Employees table. That is multiplied by the number of hours worked.
We recently decided to give an employee a raise in his ST Hourly Rate. My problem is that if John has a ST Hourly Rate of $20 and I change it to $21 then, based on the present calculations in the Timesheets table, all my historical records reflect the new $21 rate, which is not accurate.
It was suggested that I create a Rate History table linked to the Employees table that includes the following fields:
- Employee
- Effective Start Date
- ST Hourly Rate
I did that, and entered the employees' existing ST Hourly Rates using their date of employment as the Effective Start Date and then applied the new rate to the employee that is to receive the increase from $20 to $21. To test this, in the Employees table, I left the ST Hourly Rate (Original) in the Timesheets table, which is a Currency field.
I added a Rollup field that retrieves the ST Hourly Rate (new) from the Rate History table. It includes a filter where Effective Start Date is on or before today, and added an aggregation formula of MAX(values). In the Employees table, I placed the ST Hourly Rate (Original) field next to the ST Hourly Rate (Rollup). to visually confirm the data is correct. The before and after values are correct.
The Effective Start Date I used for the new rate was 2/19/24. I made a test entry in the Timesheet table with a Start/Stop Date of 2/19/24. It did the ST Gross Pay calculations correctly, but it also changed the hourly rate of the historical records to $21.
How do I preserve the records with the previous rate?