Price Update

[tidwellbj via AccessMonster.com]

I have a Work Order Database where my tbleequippricelist needs to be updated
to new pricing. When I change the pricing all of my history invoices change.
What is the easist way to update these prices. I have tried an update query
but I made a big mess.


tbleequippricelist

ID- autonumber PK
description - text
unit - text
unitprice - currency (need to update these prices, prices changed by
different amounts)

this table is one to many to tbleinstallqty. This table stores my quantities.
Which is also a subform in my main form.

IDqty - auto number PK
ID - number
InstallWOID- number
Quantity- number

this table is one to many to tbleinstallwo which holds all of the general
information.

The way my form works is you choose the "contract number" from a combo box
then it pulls up the companies information. Here is the query infor for the
subform I have when you select the pricing.

SELECT tblinstallwo.Workordernum2, tblinstallqty.IDqty, tblinstallqty.ID,
tblequippricelist.unit, tblequippricelist.unitprice, tblinstallqty.quantity,
[unitprice]*[quantity] AS Totalprice, tblinstallqty.InstallWOID
FROM tblequippricelist INNER JOIN (tblinstallwo INNER JOIN tblinstallqty ON
tblinstallwo.InstallWOID = tblinstallqty.InstallWOID) ON tblequippricelist.ID
= tblinstallqty.ID;

 

[Tom Lake]

I have a Work Order Database where my tbleequippricelist needs to be updated
to new pricing. When I change the pricing all of my history invoices change.
What is the easist way to update these prices. I have tried an update query
but I made a big mess.

For each new record, store the rate in the table after looking it up.
This way, you get the latest rate for new records and the older records
keep the older rates.

Tom Lake

 

[tidwellbj via AccessMonster.com]

I have 81 prices to change.... How do I do that?

 

[John W. Vinson]

I have 81 prices to change.... How do I do that?

How are your tables structured? You need to store the price *TWICE* - once in
your pricelist (the current price), and in a second field in the transactions
table, the historic price as of when the transaction occurred. If you have
only one price stored, that is the only price you will get.