Employee payroll forecasting. How to account for change in pay rate?

[accessbro]

I have a table called tbl_Employee. The employee's hourly rate is one of the columns. I want to create a forecasting schedule that will find the work days between two dates, multiply by 8 (hours per day) and multiply by the hourly rate.



For example, Employee A has rate of $100/hr. We forecast they will work 250 work days from January 1, 2014 to January 1, 2015 so 250*8*100= $200,000. However, on June 1, the rate for Employee A increases to $110. So the new forecast would be (125*8*100)+(125*8*110)=(100,000+110,000)=$210,000 . How can I account for something like this?

[ItsMe]

...However, on June 1, the rate for Employee A increases to $110...

Perhaps the employee rate should be stored in another table.

[burrina]

Alternatively, if you just want to run scenarios, then you could use unbound fields and simply add your criteria or use a temp table to store.

HTH