A Way To Change Products Prices Table But Keep Pevious Priced Records

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.

 

Question Info


Last updated November 13, 2019 Views 2,963 Applies to:
Answer
Answer

In this situation, you can add a Price field to the Sales table, and fill it from the products table when a product is selected.

You'd only need to store the current price for each product in the Products table.

---
Kind regards, HansV
www.eileenslounge.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer
Hans is correct. Note for example how in Northwind sample database we have [Order Details].[Unit Price] which gets populated from the Products.[List Price]. This also allows for a special one-time negotiated price, without having to update your Products table. It's the better way to go. PriceFrom and PriceTo are only useful if they serve another purpose, such as historical price comparisons between products "why does ProductA have this curve, while ProductB does not?".
-Tom.
Microsoft Access MVP
Phoenix, AZ

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.