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

Watcher05

New Member
Joined
Oct 27, 2017
Messages
11
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,006
Historical records need to have their own price field, in order to NOT get updated when prices change.
 

Watcher05

New Member
Joined
Oct 27, 2017
Messages
11
I understand what you are saying but can you please give more details of how to go about it?:)
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
The records of historical transactions should have a field for Price as part of the record.
 

Watcher05

New Member
Joined
Oct 27, 2017
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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).
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
901
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
 
Last edited:

Forum statistics

Threads
1,141,203
Messages
5,704,942
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top