Making changes lead to unintentional historical changes

[Melissa]

Help! I've created a purchase order database that captures our customers'
purchase order requests. Based on the tables and relationships I've set up
in this database, all a user has to do is enter the Item Number and the
Quantity of the item(s) the customer has requested. Then a Purchase Orders
report is generated that shows the Item Number, the Item Description, the
item's Price, and the Quantity (among other things). Everything works great;
however, when the price of an item changes, it changes the price for past
Purchase Orders as well, which messes up any historical data I might want to
capture. I understand why it does this, I just didn't know if there was
another way of going about changing the prices so that I can keep historical
information accurate. If anybody has any ideas, that would be great. I
really appreciate the help!

 

[Jerry Whittle]

Normally it's a bad idea to store derived data in a table. In other words
it's better to multiply the price by the number of items when needed.

You've found the exception to that rule. Things, such as what you are doing
and invoices, need to be accounted for at a point in time. If the price
changes, you don't want to mess up previous data.

That leaves two options:

1. Store the derived or changeable data in a table.

2. Create a complicated Price table that is linked by the item AND the date
of purchase.

I've worked with #2 before and it's hard. In your case, I'd recommend #1 and
storing the price for each purchase.

 

[martinmike2]

Melissa,

Check the relationships. And, are you storing historical data in a
seperate table? If not, maybe you should and not have it related to
anything.

 

[Jeff Boyce]

Melissa

If you need "historical" data, you have a couple options (probably more!)...

One option would be to store the price value with each Item ordered. That
way, when your price table changes, it is the new prices that are getting
stored. You'd need to modify your query that looks at the "old" orders to
make sure it picked up the newly-added price-at-sale field.

Another option is to modify your price table to include BeginDate and
EndDate fields (and possibly add a PriceID - ?autonumber). Price change?
Just note the Item, Price and Begin/End dates. Of course, your queries,
forms, reports and code will also need to be changed to use the price
associated with the PriceID that was used in the Item order.

Either way, not an effortless job, but not a deal-breaker...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP