How to build a query for cumulative total

[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

 

[Allen Browne]

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