Running sum and balance report for stock control system (1 Viewer)

[Sokkheng]

I design the stock control system that I need the report that displays the first line is opening balance (Balance BF), and have the column transfer in, column transfer out, and current balance (Stock on hand), and one more report is user can set the date rank so if the user set the date rank for the month of May so the before the month of May i need the query calculate for open balance and show the transaction of the month of May report, please guide me the steps.
thanks
Sokkheng

 

[jdraw]

See this AppInventory article by Allen Browne for ideas and insight.

 

[Pat Hartman]

The standard way to calculate the opening balance is to use a query that sums all the data before the opening date. Then you use a union query. To get the opening balance record into the same set as the details for a month. You need to add a dummy field to the query so you can ensure that the opening balance always sorts first. But do read the article for other ideas.

 

[GPGeorge]

I design the stock control system that I need the report that displays the first line is opening balance (Balance BF), and have the column transfer in, column transfer out, and current balance (Stock on hand), and one more report is user can set the date rank so if the user set the date rank for the month of May so the before the month of May i need the query calculate for open balance and show the transaction of the month of May report, please guide me the steps.
thanks
Sokkheng

Take a look at how we handled inventory in Northwind Developer Edition.

(We did not do any inventory control in the Starter Edition, so Developer it is even though it's not for first-time projects.)

It's not the most advanced possible model for inventory, but it is a sound starting place for requirements of this type.

It was derived from a seminal model elaborated by Alan Browne many decades ago.

 

[Sokkheng]

Should i create table for store open balance for every year and months?

 

[GaP42]

Review Alan Browne's model - as suggested above:
http://allenbrowne.com/AppInventory.html
Which discusses quantity on hand and stocktake adjustments - the Stocktake table allows adjustments for stock losses by product as at a date and the quantity on hand for a product is calculated from that stocktake date. Balances are not stored.
That was used as the basis for the NorthWind Developer Edition inventory control. (Alan's model was some years ago)

 

[Pat Hartman]

Should i create table for store open balance for every year and months?

Probably not. It is very easy to calculate and even if you were to store it, you would still need a union query to include it in the report. The major reason we do not store calculated values in a "transaction" database is because of the problem of having to recalculate them if some record included in the aggregation changes. You always run the risk of using data that is out of date and that can be disastrous for a business.

If you have millions of rows and dozens of years and the aggregation is slow, there is a case that could be made for archiving the detail and summarizing to one row per year for data older than x years.