cefrancke@yahoo.com wrote:[color=blue]
> Are there any ways to speed up a Cumulative sum in a query or perhaps
> another faster way to have a cumulative sum column (call a vba
> function?).
>
> For example, I want to sum up all values under 'Amount', and also have
> a field call 'Cumulative' and have the sum grow with each record. Using
> "MyTable", the query results are...
>
> ID or Date Amount Cumulative
> --------------- ------ ----------
> a 2-3-2099 5 5
> b 29-5-2099 3 8
> c 12-8-2099 6 14
> d 23-9-2099 2 16
>
> My cumulative field uses,
>
> Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates
>
> Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's
>
> This works well for relatively small recordsets, but for aggregate
> 'Group By' queries or large tables it is painfully slow.
>
> Any ideas or comments?
>
> TIA
>[/color]
The domain aggregate functions can be painfully slow.
You could try using a subquery to define your "cumulative" field instead
of using a function. Your query SQL might look something like this:
SELECT
mytable.mydate,
Sum(mytable.amount) AS SumOfamount,
(select sum(amount) from mytable as mytablecopy
where mydate<=mytable.mydate) AS cumulative
FROM mytable
GROUP BY mytable.mydate;
I think this should run reasonably quickly as long as your date field is
indexed.
Or, there are much faster replacements for Access' slow built in domain
aggregate functions which are available from
http://www.mvps.org/access/modules/mdl0012.htm 
