Saving a subforms calculated fields in a table

[davea]

Hello

I have a form setup that calculates total rates for time worked on a
job, e.g. hours worked on a job * an employees pay rate. The employees
pay rate is pulled from a subform that pulls through the employees
"current" pay rate. It works fine apart from when I have to change an
employees pay rate. When this is changed all the data entered into my
main form is recalculated with this new pay rate for all associated
records.

I want to be able to save this data with the employees old pay rate so
that historical data for the job reflects the change in pay rate. I've
set up the pay rates and jobs with start and end dates.

Any suggestions anyone?

 

[kingston via AccessMonster.com]

One option is to create a history or log table that records actual payment
for an employee and a job. Then you won't need to do the calculation and you
will have a way to verify and finalize a pay transaction. Transactions that
haven't been paid (pending and future) would be the only ones that are
calculated.

Another option is to include dates for pay rates and keep historical pay
rates. This would mean that you'd have to match pay rates to jobs according
to the dates and then perform the calculation. For example, if an employee
got a raise, you'd multiply the hours prior to the pay raise by the old pay
rate and the hours after the raise by the new rate.

 

[Al Campagna]

DaveA,
Sounds like your PayRate is a calculated or lookup field, so when the "master" employee
PayRate is changed, all your previous PayAmounts are changed.
Your Pay table should save the HoursWorked and the current PayRate in a field. The
actual AmountPaid should be a calculated field. (not saved, just displayed)

For example, (use your own method to find PayRate) using the AfterUpdate event of
HoursWorked, set the PayRate...
PayRate = Dlookup("[PayRate]","tblPayRates", "EmpID = " & EmpID)

AmountPaid is calculated...
= HoursWorked * PayRate

When PayRate changes, because you've "captured" the PayRate for each transaction at the
time the record was created, they will be unaffected by tha change.