Running Total

[94Sport5sp]

Hi:

In the A2003 database I am developing I need to calculate/store a end inventory count.

First some background. The database is a historical collections using daily imported sales data with the primary purpose of analyzing the sales that have taken place and then reconciling the sales data to a physical count.

The data in question, is in the stock table which has fields for quantity sold, quantity delivered, and physical stock count. To that I need to add over/short for the day. To calculate the over/short for the day, I need to know yesterdays closing count. I know I can calculate closing count as a function of yesterdays closing count minus todays sales plus deliveries minus physical count. It is here that my thinking runs into a road block. How do I create a starting point for yesterdays closing count?

Thanks for your thoughts.

 

[Trevor G]

In your table have you added a date field for the closing count, you could then use that in your query to work you calculation through.

 

[94Sport5sp]

In your table have you added a date field for the closing count, you could then use that in your query to work you calculation through.

Hi:

Thanks for the response.

Yes, I should have included Sales Date, Product Id, and Sales Id are all part of the Stock table.

You are suggesting I calculate the closing count for each product as I update the table and store the calculated count with each record? I had thought of that and yes I can add a field for closing count. The problem I see is when the user wants to back to a previous day and adjust their count for a past day. I would then have to recalculate the closing count for each day there after.

Thanks

 

[Trevor G]

Why would you allow a user to go back to a previous days listing, if the records are closed as the calculations have occured then I would prevent them from having access to historical data.

 

[94Sport5sp]

Why would you allow a user to go back to a previous days listing, if the records are closed as the calculations have occured then I would prevent them from having access to historical data.

Hi:

You are correct in that the sales information is historical and should not be changed. However, the physical count or the delivery count could have been entered incorrectly and after reviewing the reports the error was detected. (A paper trail is maintained on the physical count and the deliveries can be verified from invoices.) Since todays ending count depends on yesterday being correct then some way of modifying yesterdays count needed to be included.

Thanks