Running total based on two columns

[Stoic]

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:

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
 

Now, I have developed a query to look like this:

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
 

Up to now, I have the Runtot giving the results without the BeginningBalanc e using this code:

(SELECT SUM([QtyIn]-[QtyOut]) FROM [tblTransaction] t WHERE t.[TransDate]<=  [tblTransaction].[TransDate] AND t.StockID = [tblTransaction].StockID)

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
 

Everytime I add the BeginningBalanc e to the QtyOut and subtracts the QtyOut, it doubles the BeginningBalanc e in the RunTot like this:

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
 

I will appreciate any assistance.

[twinnyfo]

I would recommend that rather than having a QtyIn and QtyOut, you simply have Qty. Positive numbers are added, negative numbers are subtracted.

However, because we don’t see your entire query, we don’t know how your re coming up with your initial quantity in your query.

Please post the entire query so we cal take a look at that.

[Stoic]

@twinnyfo I thought to just use one column to represent the in and out but I would like to show them in separate columns as double entry. If there is another way to separate the entries into two columns in a query, that will also be helpful. If also the coding can be done in a report, that will also be helpful.

The query is based on the two tables - tblTransaction and tblStockCard and the only code used there is with the runtot which is

(SELECT SUM([QtyIn]-[QtyOut]) FROM [tblTransaction] t WHERE t.[TransDate]<=  [tblTransaction].[TransDate] AND t.StockID = [tblTransaction].StockID)

I included only the BeginningBalanc e from the tblStockCard into the query StockTransactio n.

Or do you mean that I post a demo of the database?

[Luuk]

If the fields QtyIn and QtyOut where stored in Qty, like twinnyfo described) than you could always do a query like:

SELECT 
  IIF(Qty<0, QtyOut, 0) AS QtySold
, IIF(Qty>0, QtyIn, 0) As QtyBought
FROM ....
 

see: https://www.techonthenet.com/access/...ed/if_then.php
or
https://www.techonthenet.com/access/...anced/case.php
or
https://support.office.com/en-us/art...0-647539C764E3

It always confues /me why Microsoft cannot give onyl 1 option to do IF/THEN/ELSE, and why they also invent/implement IIF and CASE/WHEN