Related records not updating
I have 3 tables.
Table One is Order_items with a key of SesIS
It has a field (ExtWeight) which includes the extended weight of the products ordered (based upon the number ordered and the weight per product)
Table Two is Order.
It has a key of SesID
It also has the following fields:
total weight: which is an unstored calculation defined as the sum of Ext Weight plus one. This calculation work correctly.
UPS zone: This is an unstored calculation based upon the shipping method and the Zip code. This calculation works correctly.
Com Res: This is a text field noting if the customer is a commercial or residential delivery
shipping amount: this is equal to the value of Shipping from the table UPS Charge
Table Three is UPS Charge: The values are all text or numbers, there are no calculations.
It is related to Orders by three IDs:
weight: which relates to the unstored calculation Total Weight
zone: Which related to the unstored calculation UPS Zone
Com Res: Which relates to the Com Res field.
It also has the field Shipping
When a change is made to the Line Items table: for example, changing from 5 to 10 items ordered, the total weight in Orders correctly updates. However, the values from Table three UPS Charge remains the same as when the weight was based upon 5 units. The related records from the original weight continue to be used. The relationship does not update when the unstored calculation is updated.
If I change the ZIP code, it changes the zone and everything works correctly.