How to build a query for cumulative total

  • Thread starter Thread starter Huan
  • Start date Start date
H

Huan

Hi,

I'd like to build a query to cumulative total. For example
EmployeeID Amount
A $10
A $20
A $30
B $88
B $99

I'd like to have query to cumulative total by EmployeeID such as
EmployeeID Amount
A $10
A $30
A $60
B $88
B $187

Please help and advise.

Thanks in advance.

Huan
 
This is laughably easy to do in a report.
Just use a running sum.

In a query, it's a different matter. Essentailly you use a subquery to
accumulate the values for the employee from the previous records. There are
several limitations with this approach, e.g.:

a) To identify "previous records", you must have a way to identify and sort
uniquely (e.g. by primary key.)

b) If a user applies a filter or changes the sort when viewing the query
results, the subquery no longer returns the correct results.

c) Since the subquery runs for each record, it is much less efficient than a
report.

d) Attempting to build a report that further manipulates this query is
likely to yield a "multi-level group by" error.

If subqueries are a new concept, here's a starting point:
http://allenbrowne.com/subquery-01.html#YTD
 
Back
Top