How to create an Running Sum Query?

W

Woo Mun Foong

Hi,

I have a table with ProductID, TrxNo, RecvQty, IssueQty.
ProductID, TrxNo, RecvQty, IssueQty
===================
Item1,Trx01,100,0
Item1,Trx02,0,50
Item1,Trx03,100,0
Item2,Trx04,50,0
Item2,Trx05,0,50
Item2,Trx06,100,0

What I would like to do is to contructs a query where it produce something
like the following:-
ProductID, TrxNo, RecvQty, IssueQty, RunningSum
===================
Item1,Trx01,100,0,100 <-- Running sum start from 100
Item1,Trx02,0,50,50 <-- Running sum becomes 50 after 100-50
Item1,Trx03,100,0,150 <- Running sum becomes 150 after 50+100

Item2,Trx04,50,0,50 <-Running sum start all over again
Item2,Trx05,0,50,0
Item2,Trx06,100,0,100

Anyone has any ideas on how to do it?

Thank You.
mfwoo
 
G

Gary Walter

Hi mg,

Will the "ordinality" of your text field "TrxNo"
hold up over the long haul ( why not use
an Autonumber type (say "TrxID") and then
just create a "TrxNo" when you need it....
i.e., what happens when you reach "Trx100"...
you will lose any "text ordinality" and have to
resort to

.....
ON
CLng(Mid(a.TrxNo,4)) >= CLng(Mid(b.TrxNo,4))
AND
.....

versus simpler

.....
ON
a.TrxID >= b.TrxID
AND
.....

in the following query)

If so, I believe this is what you are looking for
(change "tblmg" to name of your table):


SELECT
a.ProductID,
a.TrxNo,
a.RecvQty,
a.IssueQty,
Sum(b.RecvQty - b.IssueQty) As RSum
FROM tblmg AS a INNER JOIN tblmg AS b
ON
a.TrxNo >= b.TrxNo
AND
a.ProductId = b.ProductID
GROUP BY
a.ProductID,
a.TrxNo,
a.RecvQty,
a.IssueQty;

good luck mg,

gary
 
W

Woo Mun Foong

Works like a charm.

thank you very much.

mfwoo

Gary Walter said:
Hi mg,

Will the "ordinality" of your text field "TrxNo"
hold up over the long haul ( why not use
an Autonumber type (say "TrxID") and then
just create a "TrxNo" when you need it....
i.e., what happens when you reach "Trx100"...
you will lose any "text ordinality" and have to
resort to

.....
ON
CLng(Mid(a.TrxNo,4)) >= CLng(Mid(b.TrxNo,4))
AND
.....

versus simpler

.....
ON
a.TrxID >= b.TrxID
AND
.....

in the following query)

If so, I believe this is what you are looking for
(change "tblmg" to name of your table):


SELECT
a.ProductID,
a.TrxNo,
a.RecvQty,
a.IssueQty,
Sum(b.RecvQty - b.IssueQty) As RSum
FROM tblmg AS a INNER JOIN tblmg AS b
ON
a.TrxNo >= b.TrxNo
AND
a.ProductId = b.ProductID
GROUP BY
a.ProductID,
a.TrxNo,
a.RecvQty,
a.IssueQty;

good luck mg,

gary
Click to expand...
 
N

need help

Hi Gary

You seem to be good. I am just a beginner. So here is the question

What is that a. & b. infront of the field names? I put in my queries the
same program below but when i run it it ask me to enter data for every feild.

I want exactly the same thing as mg.

[product], [date] [trxno](auto), [in qty], [out qty], [running sum]

abc 12/8 1 200 200
abc 13/8 2 50 150
abc 15/8 3 100 50

thanks
 
Top