I am trying to figure out the best way to store or use a Sale Price. I do want to and have already established that the Order Details Table stores a unit price as a permanent record (not calculated). In a previous version of the database (previous to what I'm working on now), the Unit Price was added at the time the Order was entered. I've since added Unit Prices to the Products table, so most of the time, I want the Orders Table to be able to get that price and put it in the Unit Price Field on the Orders Detail form so that it is a permanent record sincer the established Unit Price associated with each product in the Product Table can change. I would assume I can do that with some code in the After Update event of the combo box that selects the product on the Order Details form (subform).
Aside from that, we have occasional Sales. The sales are usually a dollar amount and not usually a percentage so using a calculated control with percentage isn't going to work. I believe I will need a separate field in the Order Details Table. I'm not sure I can use it or should use it in the Products Table but maybe I'm wrong. There's then the issue of having a side by side field of Unit Price and Sale Price and how (if that's the way to go) to deal with the final calculations of the calculated controls given that the Unit Price field, may or may not be null. Anyway, I'm looking for some help in directing me to the best method for accomplishing this.
Thanks for your help