Running sum and balance report for stock control system

[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.

 

[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)