preserving values entered before a certain date

[El_Burro]

I am working on a small database that need to store time worked on a specific project by employees. Each employee is allocated a code denoting the charge rate per hour worked on a project. (eg A = £500, B=£300, C=£250.

The problem i have is that periodically the charge rates will rise, eg A becoming £550 instead of £500.

Can anybody suggest the best way to record this data so that work completed prior to the charge rate change is charged at the original value and not the new value?

Any help appreciated.

 

[ColinEssex]

Hi

Presumably you have a lookUp table to correspond to the A,B C and so on.

So if you add a startDate and EndDate for each rate you can compare the date of the job to fall between the appropriate rate dates.

Col

 

[mdemarte]

store values that change

I know that Access discourages storing calculated values and values that can be looked up in another table, but sometimes you have to store them. If your employee table is supposed to reflect the current rate, then you should probably add a field to the project table and store the rate there. Note: you can still calculate the total for the project of rate times hours, you don't need to store that calculation.

Your next concern is maintaining the existing data. If you are just running into this, then you need to update your new field with the current rate. Otherwise, you will have to update the rate for each project record somehow.

 

[The_Doc_Man]

The purist's way to do this is:

1. Make sure you have a date of action in the main table for which you are computing a rate.

2. In the table that supplies rates, add beginning and ending dates for which subject rate is valid.

3. In any query that computes the rate, use the BETWEEN...AND... operator to find the correct rate


You will basically want to pick up the rate from the table for which

a. Rate-table project matches charged project, and

b. charged project action date is BETWEEN rate-table begin date AND rate-table end date.

That way, you always have a continuous history of what rates were charged when, and you can reconstruct accounting data better. This would also eliminate questions about when rates changed for project XYZ.