Update query

[PeterR]

I am trying to update my clients labour & materials rates from april 1,
using the client & materials tables.
The update works fine in the query, but when I go into my invoice form the
new rates have updated all the previous invoices that have been created. How
can I get this to show the new rates only from april 1.
My invoice form has the clients labour rate on it, and the materials rates
are shown in a sub form.
Appreciate your help

 

[Rick Brandt]

I am trying to update my clients labour & materials rates from april
1, using the client & materials tables.
The update works fine in the query, but when I go into my invoice
form the new rates have updated all the previous invoices that have
been created. How can I get this to show the new rates only from
april 1.
My invoice form has the clients labour rate on it, and the materials
rates are shown in a sub form.
Appreciate your help

You took the advice not to store data redundantly too far. Data concerning
Invoices that might change later on needs to be saved as part of each
Invoice. If you are just "looking up and displaying" your prices and rates
then of course you lose historic data should you change those values.

So in the same table where you save the itemized list of materials and labor
you should also be saving the prices and rates. That way the Invoice record
becomes a snapshot in time of what prices and rates were when the invoice
was created rather than just pointing at what current prices and rates are.

An alternative is to do it the way you are now, but never change prices and
rates records. Instead you make changes as new records where each record
has a StartDate and EndDate to indicate when the rate or price was in
effect. I personally find that method to be way too much work to manage and
just store the data in each Invoice record.