Totals in a query?? Cannot remember how

[Michael]

This is probably really simple but I cannot remember how to get a total in a
query. I want the total of the projected production field. I used the
following sql and I keep getting the total of the row not of all the
records.

as such
lottonum | product | ProjectedProduction |T otal projected
01 rucola 30 30
03 rucola 30 30
05 rucola 30 30


What i want is either a running total or a final total
lottonum | product | ProjectedProduction |T otal projected
01 rucola 30 30
03 rucola 30 60
05 rucola 30 90

thank you for your help
Michael


SELECT Lotto.LottoID, Lotto.LottoNum, Lotto.Product,
Lotto.ProjectedProduction, Sum(Lotto.ProjectedProduction) AS total
FROM Lotto
GROUP BY Lotto.LottoID, Lotto.LottoNum, Lotto.Product,
Lotto.ProjectedProduction
HAVING (((Lotto.Product)="Rucola"));

 

[Gary Walter]

Hi Michael,

A common method for computing
the running sum over groups that I
learned from Michel is to bring the
table into the query a second time
using a non-equi join.


SELECT
a.LottoID,
a.LottoNum,
a.Product,
a.ProjectedProduction,
Sum(b.ProjectedProduction) AS RSum
FROM Lotto As a INNER JOIN Lotto As b
ON
CLng(a.LottoNum) >= CLng(b.LottoNum)
AND
a.Product= b.Product
WHERE (((a.Product)="Rucola"))
GROUP BY
a.LottoID,
a.LottoNum,
a.Product,
a.ProjectedProduction;

Of course, setting up a running sum
over a group textbox in a report is
much simpler and almost always my
first choice if possible.

good luck,

gary