Store Value or Calculate

[94Sport5sp]

[FONT=Arial, sans-serif]Hi::[/FONT]


[FONT=Arial, sans-serif]I started my project several month back to relearn Access after being away from Access for many years. One of my original question was about storing a calculated value and after some discussion I decided to store the value. Now I need to revisit this decision. [/FONT]


[FONT=Arial, sans-serif]I receive, after the fact, sales information which I import, add some extra information, and produce some meaningful reports. For the most part I am happy with my results. There is one area that I am doing circles on and hence my question.[/FONT]


[FONT=Arial, sans-serif]The area in question deals with stock on hand. This represent the quantity on hand by product group by sales day. The value to calculate is the quantity on hand at the end of the day which will then be used as the opening quantity for tomorrow. [/FONT]


[FONT=Arial, sans-serif]So the question becomes:[/FONT]


[FONT=Arial, sans-serif]Option 1: Should I store this end of day quantity and then run a query, or a dlookup, to find yesterdays end of day quantity to calculate todays end of day quantity?[/FONT]


[FONT=Arial, sans-serif]or[/FONT]


[FONT=Arial, sans-serif]Option 2: Should I create some starting amount and then run a query to calculate yesterdays end of day quantity?[/FONT]


[FONT=Arial, sans-serif]The basic structure for the stock table is:[/FONT]


[FONT=Arial, sans-serif]SalesArea(fk)[/FONT]
[FONT=Arial, sans-serif]DateOfSale[/FONT]
[FONT=Arial, sans-serif]ProductId(fk)[/FONT]
[FONT=Arial, sans-serif]ProductAdded[/FONT]
[FONT=Arial, sans-serif]ProductTransfered[/FONT]
[FONT=Arial, sans-serif]PhysicalCount[/FONT]


[FONT=Arial, sans-serif]There are other supporting tables from which the fk's draw their information and I can include layouts on them if needed. [/FONT]


Thanks

 

[AccessMSSQL]

As far as inventory is concerned definitely store the value. You want to maintain your historical data. I have an InventoryLog table that at the end of each day gets populated with the end of day values.

 

[94Sport5sp]

As far as inventory is concerned definitely store the value. You want to maintain your historical data. I have an InventoryLog table that at the end of each day gets populated with the end of day values.

Hi:


Thanks for the response.


Just to be clear, the stock table is not an inventory table. The main purpose of the stock table is to track quantity on hand and to determine if physical quantity matches (it seldom will) expected quantity. The historical sales data will be in the imported sales table. The only historical data in the stock table will be the physical count, the products added, and the products transferred. The last two item (product added and product transferred) are subject to change after review. This review may cause the end inventory count to change for a prior day.


Hm, should I split the stock table into two tables? The stock table to record the physical count, the products added, and the products transferred with a link to stock log table which records end quantity on hand and an net adjustment recorded to allow for changes to past days. Ultimately, that is my stumbling block. How do I handle changes to past days?


Thanks

 

[AccessMSSQL]

You could do that and then have the new table with these fields:
StockLogID,
StockItemID,
OnHand,
Transferred,
Added,
Adjustment,
StatusDate

 

[94Sport5sp]

You could do that and then have the new table with these fields:
StockLogID,
StockItemID,
OnHand,
Transferred,
Added,
Adjustment,
StatusDate

Hi:

You are, if I understand you correctly, suggesting summary field for OnHand, Transferred, Added, and Adjustment in the new table? Would this be summaries to date or for the current day?

Thanks