Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

Here is an example of my data in Query : [Qry Balance

Month Year Product DATE EndingQt
12 2003 OIL 5/3/04 1,00
1 2004 Grease 5/5/04 5,00
1 2004 Oil 5/6/04 2,00
2 2004 Grease 5/3/04 1,00
2 2004 Oil 5/3/04 3,00
3 2004 Oil 5/6/04 4,00
....et
I am trying to get a running total for the EndingQty
by product by Product And Month & Date. My new column should be

BalanceQt
1,00
5,00
3,00
6,00
6,00
10,00

Can't Anyone help me? plz reply in my e-mail ([email protected])
 
Andrie said:
Hello,

Here is an example of my data in Query : [Qry Balance]

Month Year Product DATE EndingQty
12 2003 OIL 5/3/04 1,000
1 2004 Grease 5/5/04 5,000
1 2004 Oil 5/6/04 2,000
2 2004 Grease 5/3/04 1,000
2 2004 Oil 5/3/04 3,000
3 2004 Oil 5/6/04 4,000
...etc
I am trying to get a running total for the EndingQty,
by product by Product And Month & Date. My new column should be:

BalanceQty
1,000
5,000
3,000
6,000
6,000
10,000
Click to expand...


Normally, I would group on the Product field and use a text
box bound to the EndingQty field and set its RunningSum
property to Over Group. But that would change the sort
order of the report by putting all the Grease together and
then all the Oil.

If you have to maintain the order you posted, then you can
use a subquery to calculate the running total. I think you
want something like this:

SELECT Q.*,
(SELECT Sum(EndingQty)
FROM [Qry Balance] As X
WHERE ((X.Year < Q.Year) Or (X.Year = Q.Year
And X.Month <= Q.Month))
And X.Product = Q.Product
) As BalanceQty
FROM [Qry Balance] As Q
 
Back
Top