Often I'll have a related table to Products that has an effective date of a price. When the price goes up, I don't erase it, I create a new record with the new price and effective date. When I need to know the current price of the date, I get the one with the most recent effective date. I usually do this with a join that is sorted by the effective date descending. Would that work?
I agree with Taylor Sharpe. A separate Prices table is a better design. My only comment is that sorting by date would include the price records with the effective start dates in the feature. I would actually flag the dates with a "current" flag and use it as a predicate in the relationship. The other option is to relate to a price record by a date range, but this will be much slower.
For complex pricing, I usually keep a separate prices table, similar to how stock price tracking works.
I keep the relationship between products and prices sorted so the newest price record is always first.
Then a lookup to pull the current price to the invoice when it's generated.
Invoices --<- InvoiceLineItems ->-- Product --<- Prices
And I see Taylor beat me to it.
What Taylor said; and if you want your line items to pick up the current price, use a relationship between LineItems and the Price table by productID, also sorted descending by date, and use a lookup / auto-enter calc.
fields for oldPrice1, oldPrice2, etc and newPrice?
The need for field1, field2 … fieldn is almost always a sign that you should rather create a related table.
Agree with the above posters.
Depending on the whole structure, a PriceChange database can be very useful. It only contains a ProductID, a timestamp and a CurrentPrice value. If you're really generous and forward thinking, you might add a "discontinued" flag which will propagate to the article, together with the moment when it occurred.
By searching into it, you can see the price variations of an item over time, or you can see which items changed their price in a given time frame.
In our special case, it's a must of form following function - clients download daily an XML with changes that were made to the compendium.ch database, which lists all the medicinals available in Switzerland and lots of related data, including their current price.
thanks everyone - got it to work.... made my Friday very productive and I learned something
Have a great weekend!