By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Running total based on two columns

P: 68
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:

Expand|Select|Wrap|Line Numbers
  1. StockID ransDate QtyIn QtyOut
  2. 1       5/2/2019  60    0
  3. 2       5/3/2019  300   0
  4. 1       5/4/3019  500   0
  5. 2       5/4/3019  0     150
  6. 1       5/4/3019  0     50
  7. 1       5/5/3019  0     200
  8. 2       5/6/3019  125   0
  9.  
Now, I have developed a query to look like this:

Expand|Select|Wrap|Line Numbers
  1. StockID BeginningBalance  TransDate QtyIn QtyOut RunTot
  2. 1       500               5/2/2019  60    0      560
  3. 1       500               5/4/3019  500   0      1060
  4. 1       500               5/4/3019  0     50     1010
  5. 1       500               5/5/3019  0     200    810
  6. 2       300               5/3/2019  300   0      600
  7. 2       300               5/4/3019  0     150    450
  8. 2       300               5/6/3019  125   0      575
  9.  
Up to now, I have the Runtot giving the results without the BeginningBalance using this code:

Expand|Select|Wrap|Line Numbers
  1. (SELECT SUM([QtyIn]-[QtyOut]) FROM [tblTransaction] t WHERE t.[TransDate]<=  [tblTransaction].[TransDate] AND t.StockID = [tblTransaction].StockID)
Expand|Select|Wrap|Line Numbers
  1. StockID BeginningBalance  TransDate QtyIn QtyOut RunTot
  2. 1       500               5/2/2019  60    0      560
  3. 1       500               5/4/3019  500   0      560
  4. 1       500               5/4/3019  0     50     510
  5. 1       500               5/5/3019  0     200    310
  6. 2       300               5/3/2019  300   0      300
  7. 2       300               5/4/3019  0     150    150
  8. 2       300               5/6/3019  125   0      275
  9.  
Everytime I add the BeginningBalance to the QtyOut and subtracts the QtyOut, it doubles the BeginningBalance in the RunTot like this:

Expand|Select|Wrap|Line Numbers
  1. StockID BeginningBalance  TransDate QtyIn QtyOut RunTot
  2. 1       500               5/2/2019  60    0      560
  3. 1       500               5/4/3019  500   0      1560
  4. 1       500               5/4/3019  0     50     2010
  5. 1       500               5/5/3019  0     200    2310
  6. 2       300               5/3/2019  300   0      600
  7. 2       300               5/4/3019  0     150    750
  8. 2       300               5/6/3019  125   0      1175
  9.  
I will appreciate any assistance.
Mar 17 '19 #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,127
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.
Mar 17 '19 #2

P: 68
@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
Expand|Select|Wrap|Line Numbers
  1. (SELECT SUM([QtyIn]-[QtyOut]) FROM [tblTransaction] t WHERE t.[TransDate]<=  [tblTransaction].[TransDate] AND t.StockID = [tblTransaction].StockID)
I included only the BeginningBalance from the tblStockCard into the query StockTransaction.

Or do you mean that I post a demo of the database?
Mar 17 '19 #3

Expert 100+
P: 1,004
If the fields QtyIn and QtyOut where stored in Qty, like twinnyfo described) than you could always do a query like:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   IIF(Qty<0, QtyOut, 0) AS QtySold
  3. , IIF(Qty>0, QtyIn, 0) As QtyBought
  4. FROM ....
  5.  
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
Mar 17 '19 #4

Post your reply

Sign in to post your reply or Sign up for a free account.