Cumulative sum of large table - DSum is unusable

  • Thread starter Thread starter Milan
  • Start date Start date
M

Milan

Dear Access users,

I deal with the following problem. In my query X, I have 2 columns: A
(ID, which is primary key) and B (sold amounts). I need to add the
column C which would represent the cumulative sum of the column B. For
example:

A B C
1 5 5
2 3 8
3 9 17

I know that I can simply get the column C by the use of function DSum
or cartesian product. The problem is that the original table is
extremely large and the calculation of query X is very time-consuming.

I would be glad if you have any suggestions. Thank you.

Milan
 
Milan said:
Dear Access users,

I deal with the following problem. In my query X, I have 2 columns: A
(ID, which is primary key) and B (sold amounts). I need to add the
column C which would represent the cumulative sum of the column B. For
example:

A B C
1 5 5
2 3 8
3 9 17

I know that I can simply get the column C by the use of function DSum
or cartesian product. The problem is that the original table is
extremely large and the calculation of query X is very time-consuming.

There are methods for doing "running sums" in queries, but as you have
discovered it is extremely inefficient against larger tables. You essentially
have to run a sub-query against every row in the base query.

Are you sure you couldn't get by with a Report to do this instead of a query?
Reports are inherently processed "per row" and something like this is trivial
when done in a report.
 
You could the RAC utility with any version of
Sql Server 2000 (including MSDE) to easily and efficiently
compute your running sum.And obviously
take advantage of the many ways Access can
communicate with Sql Server.
See thread:

Nick
Sunday, December 21, 2003 9:26 AM
microsoft.public.access.queries
Slow Query

for a similar situation.

Tools for Sql Server
RAC v2.2 and QALite @
www.rac4sql.net
 
Back
Top