Changing records in a table

[Andrea Barton]

I have two tables. One table is the main record holder (name, address, etc).
Within that table is a lookup field from the second table. The second table
contains my pricing for my products. In the prior versions of Access (2000)
I was able to change the pricing in the second table and it would not affect
the data prior to the change. Now when I change the pricing, it changes
prior fields along with the new fields. How do I go about changing the
prices without changing affecting prior data? Thank you for your time and
help on this matter.

 

[Jerry Whittle]

If you change something in a related second table, it will show the latest
values in the related records in the other table. What you have now is
working correctly, but not what you want.

Your older system must have been putting the pricing data in the main table,
or a third table, and storing it there. This is usually a bad idea EXCEPT in
cases like yours where you want to remember something at a particular point
in time.

Therefore you need to store the records with prices at a certain time. You
may want to do this in a third table.

 

[John W. Vinson]

I have two tables. One table is the main record holder (name, address, etc).
Within that table is a lookup field from the second table. The second table
contains my pricing for my products. In the prior versions of Access (2000)
I was able to change the pricing in the second table and it would not affect
the data prior to the change. Now when I change the pricing, it changes
prior fields along with the new fields. How do I go about changing the
prices without changing affecting prior data? Thank you for your time and
help on this matter.

The problem is that you're using a lookup field.

It is LOOKING UP the *current* value of the price, from the price table.

Your previous version must not have used this misfeature, because A2000 didn't
HAVE Lookup fields. It must have had code to store the current price in the
main record table.

See http://www.mvps.org/access/lookupfields.htm for a critique of what many of
us consider a misdesigned, misleading, useless feature! Your experience is
just one more of many examples of why we dislike them.

I'm fairly sure you need more tables, unless your business is such that you
never, ever expect repeat business. I'd assume that you would want at least
four tables: Customers (with name, address, etc., and a CustomerID); Products
(ProductID, product name, current cost, etc.); Orders (OrderID, CustomerID,
SaleDate, etc.); OrderDetails (OrderID, ProductID, PriceCharged, etc.).