Like to see an example of a "runningsum" being used in a query

[Vincent Johns]

OK, maybe this will do what you want. The names aren't very suggestive,
but they're intended to reflect some financial transactions taking place
on various dates. The [Amounts_ID] field is there to make the records
unique; otherwise, the Query won't work.

[Amounts] Table Design View:

Amounts_ID Date Amount
---------- --------- ------
-368813015 1/18/2006 $1.00
125521987 1/15/2006 $2.00
181471542 1/23/2006 $16.00
478760392 1/18/2006 $8.00
1954882175 1/17/2006 $2.00

The following Query will list the transactions in order by date, give
each one a sequential number, and display the running sum.

[Q_RunningSum] SQL:

SELECT Count(A2.Amount) AS CountOfAmount,
A1.Date, A1.Amount, Sum(A2.Amount) AS SumOfAmount
FROM Amounts AS A1, Amounts AS A2
WHERE (((A2.Date)=[A1]![Date])
AND ((A2.Amounts_ID)>=[A1]![Amounts_ID]))
OR (((A2.Date)<[A1]![Date]))
GROUP BY A1.Date, A1.Amount, A1.Amounts_ID
ORDER BY A1.Date, A1.Amounts_ID DESC;

The [SumOfAmount] field displays the running sum.

[Q_RunningSum]

CountOfAmount Date Amount SumOfAmount
------------- --------- ------- -----------
1 1/15/2006 $2.00 $2.00
2 1/17/2006 $2.00 $4.00
3 1/18/2006 $8.00 $12.00
4 1/18/2006 $1.00 $13.00
5 1/23/2006 $16.00 $29.00

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.