how to maintain historical records (1 Viewer)

mtmom

New member
Local time
Yesterday, 22:02
Joined
Jun 12, 2013
Messages
1
I'm rather new to Access & I hope someone can help with a problem I encountered.

I'm designing a very simple database that tracks the amount, price, total cost, etc of raw material that we buy. If the price of an item increases over time, I know that I can just update the cost in my vendor table and it will cascade to my other linked tables and update the totals for our orders. Perfect.

The problem is, how to I ensure that I can keep an accurate records of past transactions at the previous price? In other words, I don't want my tables to update items purchased in the past.

Any guidance would be greatly appreciated!

MTmom
 

rzw0wr

I will always be a newbie
Local time
Today, 01:02
Joined
Apr 1, 2012
Messages
489
I solved this by storing the Total sold price.
I don't think this is recommended by anyone however, I did not want to write a lot of code.
For me it solved several problems of refunds, taxes and future sales.

Dale
 

plog

Banishment Pending
Local time
Today, 00:02
Joined
May 11, 2011
Messages
11,613
There are 2 methods: The first breaks normalization. You would store the price of a specific order with that order. This method would require you to store the price in 2 tables --it would go with the item itself (i.e. current price) and then it would essentially copy that value to the order so it would know what the price was when purchased. If the item changed it would be updated in the item table but not in the order table because that order was purchased at that old price.

The second method requires an pricing table with dates reflecting when that price started and ended. It would look like this:

ItemPrices
ItemID, Price, PriceStart, PriceEnd
7, $14.22, 4/1/2011, 3/31/2012
7, $14.58, 4/1/2012, 3/31/2013
7, $15.01, 4/1/2012,

The price would never appear in the order table, instead it would be "calculated" in a query. You would create a function that took the ItemID and the OrderDate and it would return the cost of the item on that date.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Jan 23, 2006
Messages
15,364
There is a discussion in this thread that may be useful. There are other complexities in the thread, where there were prices per product per customer.
However, the issue is that the AgreedTo price gets stored with the OrderItem. You can't simply use a Price/Cost in a Product table -- because any changes will change any historical Order info (as you have found) and you lose that info.


See post #6 here, but the thread discussion may be useful to you generally

www.access-programmers.co.uk/forums/showthread.php?t=237359
 

smithdavid

Registered User.
Local time
Yesterday, 22:02
Joined
Jun 26, 2013
Messages
13
Make one History Table and save all records in it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Feb 19, 2002
Messages
42,981
The first breaks normalization. You would store the price of a specific order with that order.
This is not a violation. The key to understanding why is to understand the dependencies. Prices change over time and storing the price with the order fixes the price at that point in time. This method is also used because it allows for custom overrides. For example, you may offer a customer a one-time price. Storing the unit price with the order supports that. Using a pricing table to keep historical prices does not allow one-time overrides.

The simplest technique to store the price in the order detail is to include the unit price in the RowSource of the combo used to select the product. Then just copy the appropriate column to the UnitPrice field in the order detail form.

Me.txtUnitPrice = Me.cboProductID.Column(2)

The Columns of a RowSource are a zero based array so .Column(2) is the THIRD column. Don't forget to update the column count and column widths when you modify the combo's RowSource.

However, if you want to keep a price history for reporting purposes, you may also want to keep the price history table suggested by plog.
 

Users who are viewing this thread

Top Bottom