Re: Update query
- From: "Rick Brandt" <rickbrandt2@xxxxxxxxxxx>
- Date: Sun, 30 Mar 2008 13:27:11 GMT
PeterR wrote:
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.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
.
- References:
- Update query
- From: PeterR
- Update query
- Prev by Date: Update query
- Next by Date: Re: Union query and exclusion of data
- Previous by thread: Update query
- Next by thread: Re: Transpose Rows to Columns in Access Query
- Index(es):
Relevant Pages
|
