I am selling a four different levels of the same product each having a different price. On my Order Entry page I get the quantity of the item ordered and its code number. On that same order entry page I then run a calculation that looks up the Product Code and price for that code in a related table called ProductID. That price is then multiplied by the quantity to give me the Total Sale price. The problem comes when my price increases. I go to my ProductID table and change the prices of my items. What happens then is that every sale in my ten year history is recalculated with the new price rendering historical comparisons impossible.
What do I need to do so that the Total Sale price does not change every time I raise my prices?
Thank you for any assistance you can give.