Cascade update messes up previous reports

[topdesk123]

Good morning!

I have a table: EmployeePayRates - which consists of the EmployeeID; WorkCode; PayRate. The supplies a subform in the Employees form and supplies the information for time cards. It all works great UNTIL an employee gets a raise and their rate changes - then all previous time cards are updated with the new rate - which has a domino effect on other reports based on this information. Does anyone have any ideas on what I can do with this issue? I'm currently working on a From and To date but have been unsuccessful in getting it to work properly.

Thank you in advance!!

topdesk123

 

[jsteph]

You need to store a 'transactional' table, ie, one that has a record for every time the rate (or rate code, preferrably) changes per employee. This will have a date or date range, and this is the table entry that all reports should link to, based on date range.

It would be a small table with empID, RateCode, and Dates, You could use a single date or two date's for a range, either way requires some validation to avoid overlap or gaps.
--Jim

 

[topdesk123]

Thank you Jim - you confirmed what I was thinking (I think : ) ) I'm doing mental gymnastics about how to go about this but hopefully it will click after some more coffee.

Have a great weekend!!

Gina