Cumulated values

[gereva]

Hi guys,
I have a table that goes like this

Name Day Amount
Mike 01/09/2010 +12
Mike 02/09/2010 +19
Mike 03/09/2010. -8
Mike 04/09/2010 -15
Mike 05/09/2010 +22
Paul 01/09/2010 +13
Paul 02/09/2010 -9
Paul 03/09/2010 -8
Paul 04/09/2010 -25
Paul 05/09/2010 +12

[Real table has about 500.000 records]
and I need to calculate, for each name, the cumulated amount and the day over day % amount increase, like this


Name Day Amount Cum %
Mike 01/09/2010 +12 +12 n/a
Mike 02/09/2010 +19 +31 +58% Mike 03/09/2010. -8 +27 -142%
Mike 04/09/2010 -15 +12 +87%
Mike 05/09/2010 +22 +34 -246%


Paul 01/09/2010 +13 +13 n/a
Paul 02/09/2010 -9 +4 -169%
Paul 03/09/2010 -8 -4 -11%
...and so on

How can I achieve this?

This is what I have done:
Make a crosstab query with name on rows, day on columns and amount as value.
This reduces the number of records to about 8000 thus allowing an export to excel...
...But I don't like it!! I'm sure access can do this!


Any help really appreciated

Gereva

[gereva]

Sorry for poor formatting...
Posting from my bb.

[Sal_v]

Hi,
New to the forum, having some fun with some of the problems while looking for a job. My solution includes subqueries which by default are slow. Your problem has a running total with a break and uses data from the previous record. The solution does not addresses cases when the amount is 0. (you will get an error division by 0). Easy fix.
HTH