partial sum

[wvanjaarsveld]

I wan't access to calculate partial sums based on dates(formatted american style here), in particular, the amount ordered of a certain product until a certain date instead of the amount ordered at a certain date. So:

product date amount
a 01-01-2005 1
b 02-01-2005 1
a 07-01-2005 2
a 10-01-2005 1
b 01-02-2005 4
b 01-03-2005 1

Should become:

product date amount
a 01-01-2005 1
b 02-01-2005 1
a 07-01-2005 3(=1+2)
a 10-01-2005 4(=1+2+1)
b 01-02-2005 5(=1+4)
b 01-03-2005 6(=1+4+1)

I have done it with the following query:

SELECT tespartialsum.produkt_nr, tespartialsum.transdate, tespartialsum.amount, Sum(tespartialsum_1.amount) AS SumOfamount
FROM tespartialsum AS tespartialsum_1 INNER JOIN tespartialsum ON tespartialsum_1.produkt_nr = tespartialsum.produkt_nr
WHERE (((tespartialsum_1.transdate)<=[tespartialsum].[transdate]))
GROUP BY tespartialsum.produkt_nr, tespartialsum.transdate, tespartialsum.amount;

it works, but only on a restricted version of the table. I want to do it faster to be able to do it for the entire table(70000 products, 1000.000 lines).

Please help..

 

[Ron_dK]

Speeding up queries would normally require indexes.

But I guess your question was sort of answered by the Doc :

http://www.access-programmers.co.uk/forums/showthread.php?t=128328&highlight=slow+query

 

[The_Doc_Man]

We come back to the same problem: Running sums.

Your self-join is self-defeating.

Try SELECT [produkt_nr],[date], Dsum (see below) FROM tespartialsum GROUP BY [produkt_nr] ORDER BY [Date] ;

Use DSUM( "[Amount]", "tespartialsum", "[produkt_nr]=" & CStr$([produkt_nr] & " AND [Date] <= ""#" & CDate([Date]) & "#"" ") for your sum generation.

You might have to play with that punctuation a bit for the DSUM, which is sometimes a bit touchy.

Be sure you have a good index on [produkt_nr]. Beware of using [Date] as a field name. If you don't have one, put a non-unique index on the date field.