History problem........

[CEH]

I have a big problem with a very simple DB.... Inventory DB. This DB has worked fine so far at creating a "Supply order" Item is picked, quantity entered, it calculates price, so on....... Now BIG problem...... How do I maintain history of a supply order when I have a price increase on an item? The way it is set up now if you change the price of an inventory item, then look at an old supply order, it uses the new price......... I believe I need to archive the old records before making a change to the item price..... But I can't locate anything discussing this, and I have never ran into this one before. I believe my normalization is OK.... I'll attach a bmp of it.
Maybe I'm searching on the wrong keywords... This has got to be a common problem with a common solution!

Thanks

 

[Steve R.]

Though I have not done this, I think that you need to create a table that stores, by item number, the period of time that a certain price is in effect. You would then use either an SQL statement or VBA code to retrieve the price in effect at that time the order was created.

This would be attached to your tblItemList at UnitCost.
As an aside, it seems that tblItemList and tbleOrderDetail could be combined.

The Price History table would have fields similar to what is shown below. I suppose a NULL value in the EndDate field could be used to indicate that a particular price is still in effect. This may be considered "bad" programming. You could add a "current" flag to specifically identify those prices currently in effect. The current flag would be true when the price is still current and the EndDate has no assigned value. You may also want to consider a markup field since you have one in tblItemList

Item#, $cost, StartDate, EndDate

 

[pbaldy]

Personally, I would store the unit cost associated with the item in tblOrderDetail.

 

[CEH]

Personally, I would store the unit cost associated with the item in tblOrderDetail.

I think your close here....... After looking at one of the MS Access example DB's they use the 3 tables....... "Orders", "OrderDetails", and "Products". "Unit Price" is a field in both "Products" table and "Order Details" table. To make things a little clearer I think I would change the name "Unit Price" in Products table to be "Current Unit Price". So now when entering a new order your "Unit Price" would equal "Current Unit Price"
The problem I am thinking of with this is if my control source on "Unit Price" is "Current Unit Price" ....... Unit price would still change when Current Unit price was updated or changed............... IS there a simple solution to that one?

 

[Rich]

Store the UnitPrice at the time of sale along with the quantity etc

 

[gemma-the-husky]

I think there are limits to what history can be stored. Maybe in mighty systems everything is stored, including all history and so on, but in most systems some data elements must get overwritten. Do you store every old address for customers, every telephone number change, every contact change etc.

I would definitely think about capture and store the price and the product description at the time of sale and probably a lot of other stuff too. You probably need to demonstrate you can reproduce the invoice for tax reasons anyway, and its only a few bytes per invoice.

Who has practical experience of massive data stores - what do they keep?

 

[CEH]

Store the UnitPrice at the time of sale along with the quantity etc

So Rich.... What you are saying is with this new field in my OrderDetail table.. UnitPrice.... By entering a new order... assigning a new orderID and having the "UnitPrice" field with a control source of "CurrentUnitPrice" from Items table... the "Unit Price" field on old orders will not change when "currentUnitPrice" changes?

Hope that makes sense

This is what the MS example looks like it is doing. I see no code on any Unit price fields.....

 

[pbaldy]

Rich is saying the same thing I did, and yes, if you store it there it will not change when the price changes in the product table.

 

[CEH]

OK.... I've tried and tried with this..... Cant seem to get it to hold the price! Even with the new field in tblOrderDetails, then inserting it into the subform frmOrderDetailSubform, and putting the control source of the new field "UnitPrice" to "UnitCost" they still all change if I change the price in the Items table.......... Can someone tell me the error?

 

[pbaldy]

Look at the data in the detail table. No price is ever stored there; they're all zero. Your source query includes both tables, and your form is just displaying the value from the tblItemList table, not storing it in the detail table.

To avoid confusion, I would not include tblItemList in the source of the subform. I'd include the cost field in the item combo (which you already do), and on update of that combo copy the value from the combo to your cost field on the subform.

 

[dsigner]

There is confusion in this discussion between what data is actually to be stored. Do you want to create a track of item prices history or a track of supply orders (both is possible but not in one file). The key to success is NEVER depend upon a calculation in a history record.
Item price history is just a item No price and date effective. It can be assumed that a price is current until superceded by another price with a later date.
Supply history should store the Item No, quantity, line price, date supplied.
This way there are no calculations to get changed. You can however get back to unit cost for analysis (line price/quantity).
Hope this helps.

 

[CEH]

Look at the data in the detail table. No price is ever stored there; they're all zero. Your source query includes both tables, and your form is just displaying the value from the tblItemList table, not storing it in the detail table.

To avoid confusion, I would not include tblItemList in the source of the subform. I'd include the cost field in the item combo (which you already do), and on update of that combo copy the value from the combo to your cost field on the subform.

Yep Sounds like it should be easy...... but my changes aren't working..Cant seem to get the unitPrice in the detail table to take....Not sure what I'm doing wrong.. I'm thinking of just starting from scratch this weekend.... then update the info to the new DB.......

One more try....
I added "ItemPrice" to the "OrderDetail" table...... and changed the combo box name to "cboItemID"
So on the subform I have the new field ItemPrice and.......
Private Sub cboItemID_BeforeUpdate(Cancel As Integer)
Me.ItemPrice = Me.cboItemID.Column(3)
End Sub

I get........ NOTHING ???????????

 

[CEH]

Think I may have it solved..... On the afterupdate event of the combo copy from field to field and not from the combo column seems to work........
We'll find out when I run some more test data in it....... Thanks for all your help!
I'm updating this post for others who dive into an inventory application. First, all is working fine now thanks to all the guys help. With my experience on this one I would say it is critical to have 2 price fields. One in your table that stores the inventory item data and another in the table that stores your transaction data. And of course you need the code to update your item cost table to your transaction table. This enables you to keep the current cost of the item in the Items table and your previous cost when an item was sold in your detail (transaction) table. After 20+ years in accounting I know that keeping an acurate history of inventory is a MUST! Your history of profit and loss is tied directly to inventory.
Thanks again for everyones help.