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.).

 

[KenSheridan via AccessMonster.com]

If your description is correct there is no way in which prior values would
have been maintained following changes to those values in the referenced
table. You would need a column in the referencing table to hold those values,
not a column to reference the values in the referencing table as a 'lookup
field'.

However, unless for each name, address etc in the referencing table there can
be only one value from the products table, i.e. the relationship is many-to-
one (which is unlikely) the referencing table will contain redundancy as it
would be necessary for each name, address etc to be repeated for every
product to which that customer (I'll assume for this example that this is the
entity type represented) relates.

A more likely scenario is that the relationship is many-to-many. e.g. each
customer can order one or more products and each product can be ordered by
one or more customers. To model a many-to-many relationship you'd need a
third table, CustomerSales say with two foreign key columns e.g. CustomerID
and ProductID, and other columns such as UnitPrice, Quantity and SaleDate.
The current UnitPrice value would be obtained from the Products table when
the ProductID value is inserted into the CustomerSales table and assigned to
the UnitPrice column in CustomerSales. The price in this table would then
remain static when the UnitPrice value in Products is changed. The Customers
table would thus contain one row per customer, with the multiple sales per
customer modelled in the CustomerSales table.

You'll find an example of this in the sample NorthWind database, in the Order
Details subform. In this code in the ProductID control's AfterUpdate event
procedure looks up the current UnitPrice of the selected product in the
Products table and assigns it to the control bound to the UnitPrice column in
the Order Details table.

Ken Sheridan
Stafford, England