PLEASE HELP: running sum special

[bertenbert]

Dear all,

Would need help with the following:

Would like to create a running sum, but one that starts all over again with each new product.

MONTH PRODUCT PRICE RUNNNING SUM
1 Product A 55 € 55 €
2 Product A 45 € 100 € (55 € + 45 €)
3 Product A 30 € 130 € (100 € + 30 €)
4 Product A 15 € 145 € (130 € + 15 €)

5 Product B 100 € 100 € (and not 245 €)
6 Product B 300 € 400 €

I already have part of the running sum:

DSum("[PRICE]";"NAME TABLE";"[MONTH]<="&[MONTH]&"")

This running sum keeps on counting:
5 Product B 100 € 245 € (should be 100 €)
6 Product B 300 € 545 €

I should probably introduce a new criterium but I don't know how.

Please help.

Thanks,

Bert

 

[MattP]

I don't have a handy Access database available but I was able to do it using T-SQL like this:

SELECT a.Product, a.Price, SUM(b.Price) "Running Total"
FROM TableName a CROSS JOIN TableName b
WHERE a.Price >= b.Price AND a.Product = b.Product
GROUP BY a.Product, a.Price
ORDERY BY a.Product

Hopefully you can adapt it to your situation.