94Sport5sp
Registered User.
- Local time
- Today, 06:59
- Joined
- May 23, 2012
- Messages
- 115
Hi:
I am creating a table to store perpetual inventory information. Just to be clear, I am not managing the inventory but rather recording what has taken place. On a daily basis, I import several text files which provide information on what was sold, including product, total quantity, and total amount. To this data my system will add a physical inventory count and quantity of product added to the inventory.
Because the inventory is perpetual there is no real start point and definitely no ending point. For each day I would then display information showing [Opening Quantity, Quantity Sold, Quantity Received, Expected Quantity on Hand, Actual Quantity on Hand, Difference between Expected and Actual], and this information would be displayed by area, then by day, then by product .
Originally I created the table to store a calculated field for Opening Quantity which is yesterdays Actual Quantity on Hand. After reviewing this I identified two problems. There is no way to verify that the value for Quantity Received or that the value for Actual Quantity on Hand are accurate. This leads to a second problem of how to correct past errors (usually found by management when they review the results) because past errors affect todays Opening Quantity.
In addition to the above I am also trying to design a way to create a variable starting point. That is old records will be deleted and the Actual Quantity on Hand for those records will be deleted.
Thanks
P.S. I was going to create a running total and I posted a question on that in the tables section. However, the more I think about it the more I think it is a design issue. Once I get the correct design then the rest should fall into place.
I am creating a table to store perpetual inventory information. Just to be clear, I am not managing the inventory but rather recording what has taken place. On a daily basis, I import several text files which provide information on what was sold, including product, total quantity, and total amount. To this data my system will add a physical inventory count and quantity of product added to the inventory.
Because the inventory is perpetual there is no real start point and definitely no ending point. For each day I would then display information showing [Opening Quantity, Quantity Sold, Quantity Received, Expected Quantity on Hand, Actual Quantity on Hand, Difference between Expected and Actual], and this information would be displayed by area, then by day, then by product .
Originally I created the table to store a calculated field for Opening Quantity which is yesterdays Actual Quantity on Hand. After reviewing this I identified two problems. There is no way to verify that the value for Quantity Received or that the value for Actual Quantity on Hand are accurate. This leads to a second problem of how to correct past errors (usually found by management when they review the results) because past errors affect todays Opening Quantity.
In addition to the above I am also trying to design a way to create a variable starting point. That is old records will be deleted and the Actual Quantity on Hand for those records will be deleted.
Thanks
P.S. I was going to create a running total and I posted a question on that in the tables section. However, the more I think about it the more I think it is a design issue. Once I get the correct design then the rest should fall into place.
