Update products with new prices but keep old prices in the prevoius records

[Dorethy]

Hello,



I need to update products with the new prices but keep old prices in the prevoius records.

I have a Customer, Event Details, Events, Products and the Promoter Tables, when I update the Products table prices the change also in the old reports. Is there an easy way to have the future records only updated with a new prices?

[Micron]

You could have repeating rows in your products pricing with a date field to archive it. If null, the price is current. If not, it gives you a marker that you can match up with an invoice or PO. My db background is mechanical and ISO related databases, so I've never really understood this problem. It's always been my understanding that your PO or invoice line item contains whatever price was current at the time as a field record, not as a reference to the price data. That way if it was $1 at the time, it remains $1 on the report/invoice/PO forever.

[Dorethy]

Thank you for the answer. As I am new to access i need to clarify some details,

You could have repeating rows in your products pricing with a date field to archive it

Do you mean creating new table with a date column in it?

If null, the price is current

Do you mean here that I need to create a query and asingn a Date to with a null creteria?


If I sent you the file maybe this could be more helphul?


Thank you very much!

[Micron]

1) means you add a date field to your existing table (e.g. EndDate < notice the lack of spaces and no using reserved words such as "Date"). If EndDate has no value, the price is current. 2) When you need it, you retrieve it with a query but add the criteria as (e.g) WHERE ProdID = 65 AND EndDate Is Null. Your comment suggests you base your forms and reports on tables, which is something I rarely do.

However, using the date method means you have a new record where all the product field data is repeated each time you archive a price - not what I would do. To avoid repeating data, you'd need a separate table whose FK (foreign key) field contains the Product ID PK field but you'd still need a way to archive the price.

You can post a db (usually it must be zipped after you run a compact/repair on a copy) but as I said, commerce wasn't my thing. Posting will help others to help you. What I'm not understanding is why people ever do it this way, so I'm hoping to learn of a valid reason if there is one, by replying in the first place. IMO the thing to do is have your current price and that value becomes the value in the price field of other tables such as PO and invoice. You'd then be able to look back 10 years ago to see what something was priced at, but I might be out to lunch with that notion.

[orange]

Further to micron's response, you may find this post on AgreedUponPrice helpful. I have referenced the same concept in various threads when discussing historical invoice info, loyalty programs, specials, clearance sales etc.

Good luck.

[Gicu]

I use a similar approach as that described by Micron with the difference that the Products table would have an EffectiveDate (or PriceStartDate) instead of an EndDate. I usually then create a TOP 1 query sorted ascending by ProductID and descending by the EffectiveDate with parameters coming from you form (<=Forms!frmYourForm!InvoiceDate). That would return the latest price that was in effect at the time of the invoice. You can also get that by using domain aggregate functions(dLookup, dMax). If using this approach then you can add future prices and produce invoices for future events with different prices for each.

Cheers,

[Dorethy]

Thnak you!

[ArviLaanemets]

Another approach is to have a table for current prices, and write the current price into sales (and/or purchases) log when the log entry is created. So whenever you need to get the price of product at certain date, you query it from sales log. The caveat is, this approach assumes excellent sales registering discipline, or a possibility to enter the price manually when the log entry is made later.

[Welshgasman]

Now crossposted https://www.access-programmers.co.uk/forums/threads/update-a-price-but-retain-old-price-for-older-records.319841