Wanting to Update Tables without Affecting Old Records

[Brian]

I produce invoices for the biling of employees labour time. The invoice will
get the correct rate to charge from a look-up table of employee's rates.
When I choose to increae or reduce the values in the look-up table, how do I
prevent all the previous invoices that have been audited from changing.
There must be an easier way other than having to create a new look-up table
and changing all control references in queries, forms and reports.
I have been advised that the answer lies with some sort of date-based
control but I can't quite work out how to do this.
Any pointers would be much appreciated.

 

[Beetle]

The table that stores the Invoice data should have a field that stores
the rate. The value for the rate would be retrieved from the lookup
table, but it needs to be stored in the Invoice table as well, so that
each invoice record will reflect the rate that was in effect at the time
the invoice was generated.

 

[John Spencer]

A second option is to add two fields to the rates table that specify the
start and end date of the rate. Then assuming you have an effective
date in the Invoice table, you can join to the rates table using a join
on whatever you are using now plus the invoice date being within the
specified range for the dates.

RatesTable
EmployeeRate - the rate
EmployeeType - the type of employee that this rate applies to
StartDate - the start Date for the rate
EndDate - the end date for the rate

SELECT Invoices.InvoiceID
, EmployeeRate
FROM Invoices INNER JOIN Rates
ON Invoices.EmployeeType = Rates.EmployeeType
AND Invoices.InvoiceDate >= Rates.StartDate
AND Invoices.InvoiceDate <= Rates.EndDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Brian]

Apologies for the delay in acknowledging your contributions, it's down to the
UK/US time difference.
I will try both suggestions and see which works best in my particular
situation.
Thanks to both gentlemen.