Baffled...Can someone explain this?

[Mike G]

I'm new at this but I'm sure some of you know exactly what I need to do.

Trying to use the Personnel Records template and the Time Card template to set up a Time Card database for a small business. I've created a relationship between the two with little difficulty. I added a Hourly Wage field to Personnel Records for each emlpoyee. Also added a calculated field to Time Card to show total $$$ for hours posted. So far so good. What I don't understand how to do is IF and WHEN an employee wage changes and you change it on their Personel Record it updates PAST weeks of hours posted.

Example...

If Employee A works 20 hours at $6 from 10/4/2004 - 10/8/2004 the $120 shows fine. If Employee A gets a raise to $7 and that is updated in his personel record and he works 20 hours at $7 from 10/11/2004 - 10/15/2004 the $140 shows fine for that week. BUT, if you go back and look at the week beginning 10/4/2004 it has updated to $7 and the $140. How do you maintain the original post created with $6 and $120?

Thanks

[Ender]

You'll need to keep an Hourly Wage field in your time cards, and set it as a lookup from Personnel Records. This will keep it static for past records even if the Hourly Wage is changed Personnel Records.

Alternatively (with a bit more work), you could have another table track wage changes, and refer to that (by a date range relationship) to calculate the pay for a given payperiod.