how to updating product tables with new yearly pricing - MS 2013

[Watcher05]

Hi all

I've been using this guide http://web.pdx.edu/~gerbing/325/Resources/Access.pdf which gives reasonable instructions to setup a simple invoicing database, which I've done. https://drive.google.com/open?id=1hLO41QMUGE6vL-76qIQyT_d6ycZ8bVeo


If i update the the product table with new pricing it seems to affect all historical records. I'm sure there is an easy way to do this but I don't want to commit to this database and 12 months down the track i cannot update new financial year pricing. You can see i'm very new to access.

Thank Kindly

 

[ranman256]

Historical records need to have their own price field, in order to NOT get updated when prices change.

 

[Watcher05]

I understand what you are saying but can you please give more details of how to go about it?

 

[xenou]

The records of historical transactions should have a field for Price as part of the record.

 

[Watcher05]

but i don't know how to do that in the current table i have. Do i start a new table for the financial year or add it into the existing table?? thanks heaps for your feedback.

 

[xenou]

Usually with something being sold there is an 'order details' table that has information such as: Qty, Price, ItemID. So price is just one of those fields (note: I think in this case he calls it order line).

 

[welshgasman]

When you enter a new.amend and existing record, you get the 'current' price of the item, BUT you store it in the record at the same time, not link back to the Product price.
You could have the Products with an effective date and then look for the record with the highest date for a product to get the latest price. That way you can see the change in prices over time.
Or more simply, just change the prices for the products, and as soon as you do that, you will be using the updated prices.

Investigate DLOOKUP as one option to get the relevant price. Another would be bringing the price into the source for the product and then copy it to the price control.?
I would be using a combo for the Product, hiding the ProductID from the user and then the second method for getting the price. However I am not that experienced in Access I have to admit.

HTH