I have a Products Table which includes prices (approx 200 items). Some of the prices need to change but obviously existing records need to be maintained with the previous prices. The [ProductsTable] is used to create records for sales and invoicing purposes.
I have [PriceFrom] and [PriceTo] date columns alongside each product with the [PriceTo] set to 2099. When a price changes the [ToDate] is set to today and I create a new entry in the table for the same product with new dates. A query filters my [ProductsTable] to produce a ComboBox with the current Product Prices.
I'm thinking there must be an easier way to achieve this current price list. The [ProductsTable] is a great deal larger than it need to be with so many repeated entries. I do not need to keep an historical records of earlier prices other than to maintain
the integrity of ealier records..
Additionally I want the operator to have a simple way of updating the product prices without the need to poke around in the tables and queries section. Is ther e a way to "Fix" the price in a record once it has been saved and not be affected by future price changes?
I have looked at many Access help pages but haven't yet got the answer.