Update a price but retain old price for older records (1 Viewer)

[Rania01]

Dear All,
I would like to change prices for all items, the thing is I don't want to change the of any historical records, only
new records.
All help will be appreciated.

Rania

 

[jdraw]

See this older post for ideas.

 

[The_Doc_Man]

The usual way to do this is to have a "historical" price table, You have some fields in this table:

tblPriceHistory
ProductID - whatever format you use for unique product ID, it goes here in the same format
Price - whatever format you use for prices (Currency or Double or something else), it goes here in the same format
FirstDate - put here the first date, in date format, on which this price could be used.
LastDate - put here the last date, in date format, on which this price could be used.

NOTE that you can put a future date in LastDate for the current price. In the past I have used a date of #31-Dec-9999# for this purpose. When you use this method, the dates can NEVER be allowed to overlap. That is if you change prices on Tuesday, the prior price record's last date has to be the Monday and the new price's first date is the Tuesday.

Then use a query to join your product table to the history table:

SELECT P.ProductName, H.Price, <<<other fields you might need>>>
FROM tblProducts P INNER JOIN tblPriceHistory H
ON tblProducts.ProductID = tblPriceHistory.ProductID
WHERE Date() BETWEEN H.FirstDate AND H.LastDate ;

 

[Rania01]

Hello,
If you have time could you check my file and fix it?
So I can change the price

 

[Pat Hartman]

@Rania01 We're here to help you not to do your work for you. You are asking us to do for free something you will be paid for.

At least try to follow the directions offered by Doc.

 

[dorethy]

Hello Mr. The_Doc_Man,

Your instruction seems easy but i am a new in access and I need to ask more question if you could assist me I would be grateful.

I have a Customer, Event Details, Events, Products and the Promoter Tables, and the same issue with the user Rania01, I need to update the Wholesale and Sale Prices without changing the data in old records.

When you say that I need a "HistoricalPriceTabel" that means that I need to copy the existing "Products" table and then add the StarDate & EndDate to it or I could use the defualt "Prducts" table and just add the SDate & EDate to it? And the date "#31-Dec-9999#" should be add in a query? I have a Query for the event already, please see attached screenshot, or I need to create a new query for this purpose?
I am trying to understand how "the lock price" is working but without any success, any good tutorial would be helpful too. Thank you!

 

[Mike Krailo]

Add a new field EPrice in event details table. Then create update query to update that field for all past records. Then, change your price in products table.

In the form for event details, use an after update event on the product ID combo so it automatically fills in EPrice field for you.

 

[arnelgp]

in my opinion, another table for historical prices of products should be mayde.
and you connect event detail to this New table since it holds the price for that
date range.
or you may include the price to your event detail table.

 

[dorethy]

Thank you very much for the instructions all of you!
I am following the Mike Krailo instruction, I have added the "EWholesalePrice" & "ESalePrice" to the "EventsDetails", I am trying to create an update query for the future prices but it changes the prices in every Event where the product was sold. please see the screenshot of the record, what I am doing wrong here? Thank you again.

 

[arnelgp]

you should get Events table involved (joined) since it has the date in it.

 

[dorethy]

you should get Events table involved (joined) since it has the date in it.

Thank you arnelgp, I did this, but without success, I think I need to say somehow update the prices on the new event only... I am confused

 

[arnelgp]

since it is "future" you don't Update it. since updating applies to existing records.
you do it in Form. see this demo. the event table and event details table is but minimal.
what is important is the product_price table.

see the AfterUpdate event of product combobox on the subform.

 

[The_Doc_Man]

Arnelgp's suggestion of another table is, to me, the correct way to go. You HAD a table with product ID, product description, and price - but now you find that the price is not permanent. If you need the THEORY of why you need to do it, I can tell you. However, it will be simpler if what you do is build a table that you will later use with a JOIN query.

Before you needed time-sensitive prices: (NOTE: Prime Key noted by underscores)

ProductTbl: ProdID, ProdName, ProdDesc, ...., ProdPrice
Invoice: InvID, InvDate, InvCustomer, ....
InvDetail: InvID, ProdID, InvQuantity

and a query that JOINs product to invoice and detail:

SELECT I.InvID, I.InvDate, I.InvCustomer, P.ProdID, P.ProdPrice, ...<<<other fields>>>
   FROM ( ( InvDetail ID INNER JOIN ProductTbl P ON ID.ProdID = P.ProdID ) INNER JOIN Invoice I ON I.InvID = ID.InvID )... <<<other syntax like WHERE or ORDER BY or GROUP BY>>>

After you needed time-sensitive prices:

ProductTbl: ProdID, ProdName, ProdDesc,....
ProdPricTbl: ProdID, ProdPrice, PP1stDate, PPLastDate
Invoice: InvID, InvDate, InvCustomer, ...
InvDetail: InvID, ProdID, InvQuantity, ...

and in ProdPricTbl, PPLastDate defaults to #31-Dec-9999# (the largest date Access can recognize in conventional format.)

Now the query gets a LITTLE BIT trickier

SELECT I.InvID, I.InvDate, I.InvCustomer, ID.ProdID, PP.ProdPrice, ... <<<other required  fields>>>
    FROM ( ( InvDetail ID INNER JOIN Invoice I ON ID.InvID = I.InvID ) INNER JOIN ProdPricTbl PP ON ID.ProdID = PP.ProdID)
    WHERE I.InvDate BETWEEN PP.PP1stDate AND PP.PPLastDate ....<<<other required syntax>>>

In fact, there are other ways to do this. But this is, I think,the simplest one to understand. Basically, you SPLIT OUT the price info from the product table because suddenly, price has a dependency that does not match the dependency of the other items in the product table. Which means it has to be treated separately. So you have a table of product prices. When the price changes, you do two things. On the day the price changes, you edit the most recent price entry to have today's date as the PPLastDate, then create a new record with the same ProdID but a new ProdPrice and a new PP1stDate (tomorrow?) - but let the PPLastDate take the default I described. Now old prices stay unchanged, new prices are changed for each product that HAD a price change, and NO OTHER QUERY OR CODE needs changing. The only catch is that at NO TIME can a product have a pair of price entries for which the PPLastDate of the price just changed matches the PP1stDate of that same product's new price. I.e. there can be NO OVERLAP in the price dates.

In fact, you will probably have to play with this for a while because the sources of those items might actually be from combo boxes on forms. But if I start adding all sorts of bells and whistles of that type, I'll make the offering that much harder to read and UNDERSTAND. So don't expect this to be more than a guideline of the approach. I have no doubt the final product will be different. However, by breaking it down for you, I applied the same tactic used by Julius Caesar in his Gallic Wars - "divide and conquer." I just divided the time-variant price part for you.

 

[Gasman]

Hello Mr. The_Doc_Man,

Your instruction seems easy but i am a new in access and I need to ask more question if you could assist me I would be grateful.

I have a Customer, Event Details, Events, Products and the Promoter Tables, and the same issue with the user Rania01, I need to update the Wholesale and Sale Prices without changing the data in old records.

When you say that I need a "HistoricalPriceTabel" that means that I need to copy the existing "Products" table and then add the StarDate & EndDate to it or I could use the defualt "Prducts" table and just add the SDate & EDate to it? And the date "#31-Dec-9999#" should be add in a query? I have a Query for the event already, please see attached screenshot, or I need to create a new query for this purpose?
I am trying to understand how "the lock price" is working but without any success, any good tutorial would be helpful too. Thank you!

Crossposted https://www.accessforums.net/showthread.php?t=85556&highlight=prices

 

[dorethy]

Thank you very much arnelgp, I will check the example.

 

[Cotswold]

Generally if this is important, you should consider multiple price lists. Their control isn't trivial but it will make annual updates and spasmodic changes easier for the users.