Coping with price changes - HELP!

  • Thread starter Thread starter Andrew M
  • Start date Start date
A

Andrew M

Guest
I am creating a database that models an oil company's orders/sales etc . Some of tables are linked as follows : Oil table linked to customer table[1 to many] which in turn is linked to the order table [1 to many].

I need to store the oil price from my oil table in the order table when I create a new order(through the order form) so when the price changes in the oil table previous order records will still contain the old price. I have tried using the oil price in the oil table as my source for the order form but I can't then get it to save this value into the oil price field in the order table - should I be using a query as the source for the form instead?

Any help gratefully received - this has had me stuck for ages!
 
If you are using a combo box for the oil prices, you can use the ADDNew record (ASk Clip)
 
This is the classical example for a situation in which you have to violate the rules of normalization:

The current prize has to be copied into the orders (details) table.

This is the only way to be able to change the price of the product without changing the price in each and every old order.

In general you encouter this problem in POS applications.
We (in Europe) will have a big problem with poorly designed applications because the new currency (EURO) will be introduced on 1/1/2002. I already converted twelve applications (not designed by me) since the beginning of the year and I fear that there will be more to come.

So be aware of this problem and design your tables in the correct manner right from the beginning!
 
Actually, there is another way to store the prices.

Make each oil price its own record in a table, with beginning and ending dates ("effectivity dates") for that price. Then you can calculate the price at any given point in time. You can also put price changes in ahead of time (give them an effectivity date in the future).

You still may want to store the price with the order, if your sales people give the customers "special" prices or discounts.
 

Users who are viewing this thread

Back
Top Bottom