Hi,
I know there are lots of answers provided on here regarding running total, however, I have not seen the specific answer to what I need for my database.
Now, I have a database built in access 2013. In my database, I have two tables (tblTransaction and tblStockCard).
The tblTransaction table holds all the +- transactions while the tblStockCard holds the list of items in stock with their opening balances. The tblTransaction table has columns as listed below:
Now, I have developed a query to look like this:
Up to now, I have the Runtot giving the results without the BeginningBalanc e using this code:
Everytime I add the BeginningBalanc e to the QtyOut and subtracts the QtyOut, it doubles the BeginningBalanc e in the RunTot like this:
I will appreciate any assistance.
I know there are lots of answers provided on here regarding running total, however, I have not seen the specific answer to what I need for my database.
Now, I have a database built in access 2013. In my database, I have two tables (tblTransaction and tblStockCard).
The tblTransaction table holds all the +- transactions while the tblStockCard holds the list of items in stock with their opening balances. The tblTransaction table has columns as listed below:
Code:
StockID ransDate QtyIn QtyOut 1 5/2/2019 60 0 2 5/3/2019 300 0 1 5/4/3019 500 0 2 5/4/3019 0 150 1 5/4/3019 0 50 1 5/5/3019 0 200 2 5/6/3019 125 0
Code:
StockID BeginningBalance TransDate QtyIn QtyOut RunTot 1 500 5/2/2019 60 0 560 1 500 5/4/3019 500 0 1060 1 500 5/4/3019 0 50 1010 1 500 5/5/3019 0 200 810 2 300 5/3/2019 300 0 600 2 300 5/4/3019 0 150 450 2 300 5/6/3019 125 0 575
Code:
(SELECT SUM([QtyIn]-[QtyOut]) FROM [tblTransaction] t WHERE t.[TransDate]<= [tblTransaction].[TransDate] AND t.StockID = [tblTransaction].StockID)
Code:
StockID BeginningBalance TransDate QtyIn QtyOut RunTot 1 500 5/2/2019 60 0 560 1 500 5/4/3019 500 0 560 1 500 5/4/3019 0 50 510 1 500 5/5/3019 0 200 310 2 300 5/3/2019 300 0 300 2 300 5/4/3019 0 150 150 2 300 5/6/3019 125 0 275
Code:
StockID BeginningBalance TransDate QtyIn QtyOut RunTot 1 500 5/2/2019 60 0 560 1 500 5/4/3019 500 0 1560 1 500 5/4/3019 0 50 2010 1 500 5/5/3019 0 200 2310 2 300 5/3/2019 300 0 600 2 300 5/4/3019 0 150 750 2 300 5/6/3019 125 0 1175
Comment