Running sum running by date order

[×ייל המבולבל]

I have the following problem.
I would like to create a running sum on a form or a query. It should sum
PurchaseAmount but I have a problem with the where criteria of the Dsum.

I use now (easier to check results):
DCount("*","temp",("[Saved] <=" & "#" & [Saved] & "#" & "and [PurchaseID]<="
& [PurchaseID]) & "or" & (("[Saved] <=" & "#" & [Saved] & "#" & "and
[PurchaseID]<=" & [PurchaseID])))


The problem is summery is calculated in order of purchase ID, Regardless of
the purchase date. I would like to be able to make a running sum to summarize
all purchases by order of date (and when there are purchases done on the same
date, summarize by purchase ID.

What I get is this:
Saved Amount ID RunningSum Count

08/01/07 28512 1 28512 1
09/01/07 32550 4 61062 2
17/01/07 117130 282 178192
3
22/01/07 101330.8 292 279522.8 4
-------------------------------------------------------------
25/01/07 47282 310 502423.6 6
28/01/07 175618.8 296 455141.6 5
-------------------------------------------------------------

Should look like this

Saved Amount ID RunningSum Count

08/01/07 28512 1 28512 1
09/01/07 32550 4 61062 2
17/01/07 117130 282 178192
3
22/01/07 101330.8 292 279522.8 4

 

[Michel Walsh]

SELECT a.saved, a.id, LAST(a.amount), SUM(b.amount), COUNT(*)

FROM myTable AS a INNER JOIN myTable AS b
ON ( a.saved > b.saved
OR ( a.saved = b.saved AND a.id >= b.ID) )

GROUP BY a.saved, a.id

ORDER BY a.saved, a.id






The ORDER BY clause is optional, but added to allow easy checks.


I assume there is one and only one record for each possible group of values
{ saved, id }

Replace myTable with your real table name ( twice ).



Hoping it may help,
Vanderghast, Access MVP