Running Total Query Help 1

[JW61]

I have the following query:

SELECT A.ITEMCODE, A.DESCRIPTION, A.OHQTY, B.TotSum AS BAL
FROM qryBalls AS A INNER JOIN [SELECT ITEMCODE,  Sum(OHQTY) AS TotSum FROM qryBalls GROUP BY ITEMCODE]. AS B ON A.ITEMCODE = B.ITEMCODE;

Which returns these results:

ITEMCODE     DESCRIPTION  QHQTY    BAL
112840008    5B 9/32       40      195
112840008    5B 9/32       70      195
112840008    5B 9/32       85      195
112840021    5B 9/16       27       27
120840242    2B 1/4 10     54      509
120840242    2B 1/4 10     65      509
120840242    2B 1/4 10     87      509
120840242    2B 1/4 10    303      509

What I want is:

ITEMCODE     DESCRIPTION  QHQTY    BAL
112840008    5B 9/32       40       40
112840008    5B 9/32       70      110
112840008    5B 9/32       85      195
112840021    5B 9/16       27       27
120840242    2B 1/4 10     54       54
120840242    2B 1/4 10     65      119
120840242    2B 1/4 10     87      206
120840242    2B 1/4 10    303      509

Can anyone help me?
Thanks,
JW

 

[PHV]

Search this forum for running sum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JW61]

Thanks ... I have done that and have read dozens of threads. For some reason I just can't figure it out.

 

[PHV]

What is the SQL code of qryBalls ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hap007]

Dear JW,

I do not think that what you want to do is possible with the 'SUM' command. Sounds like you want a 'Running Total' that resets on a change of ITEMCODE.

I might suggest an alternate approach.

1) Use the query you have (without the SUM) to build a temporary work table.
2) Then write a small VBA code module to open the temporary table and process the table
3) Check ITEMCODE For a match and either add to a running total, or reset running total based on a match or not.
4) Read each record, Test if ITEM Code matches and if so, do an .EDIT ,insert your running total, then .UPDATE and .MoveNext

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[JW61]

qryBalls SQL:

SELECT OH.ITEMCODE, OH.DESCRIPTION, OH.OHQTY, OH.SUBINVENTORY, OH.LOT_NUMBER, OH.STATUS, OH.xLOT, OH.INCREMENT, OH.BALLDIA, OH.CONFORM FROM OH INNER JOIN tblBallSubInv ON OH.SUBINVENTORY = tblBallSubInv.Ball_Sub_Inv
WHERE (((OH.ITEMCODE) Like "???84*" Or (OH.ITEMCODE) Like "???87*" Or (OH.ITEMCODE) Like "???93*" Or (OH.ITEMCODE) Like "???94*" Or (OH.ITEMCODE) Like "???96*" Or (OH.ITEMCODE) Like "307*"));

Thanks Hap ... Had thought of that but was trying to do in a query if possible.

 

[PHV]

A starting point:

SELECT A.ITEMCODE, A.DESCRIPTION, A.OHQTY, Sum(B.OHQTY) AS BAL
FROM qryBalls AS A INNER JOIN qryBalls AS B ON A.ITEMCODE = B.ITEMCODE AND A.OHQTY >= B.OHQTY
GROUP BY A.ITEMCODE, A.DESCRIPTION, A.OHQTY

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886