Running Sum in Query

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Recently, I have used a "Running Sum" in a report.

I am wondering if I can do the same in a query (maybe w/ an unbound field).

Does anyone know if Access has a built-in solution for that or as to how I
can create a "work-around" that would allow me to see a running subtotal in
a query?


Thanks in advance,
Tom
 
There is not a nice, efficient solution for this.
You are going to end upt with a subquery or DSum() calculated field in your
query, and it will only work correctly if the query sorting remains
unchanged.

Assuming:
- a table named Table1,
- query sorted by the primary key named ID,
- collecting running sum on field name Amount,
you would type something like this into the Field row of your query:

(SELECT Sum(Amount) AS SumOfAmount
FROM Table1 AS Dupe
WHERE Dupe.ID <= Table1.ID)
 
Allen:

Thanks for the prompt response... I got this to work as you suggested.

Unfortunately, as you mentioned, it only works if the order remains
unchanged... and this is where my problem lies.

I working on a table with Weighted Averages... once changes apply (e.g.
"something that was considered LOW PRIORITY may become HIGH PRIORITY...
hence, the order of items changes all the time.

I'm sure you would have told me if there is a "trick" to also accomodate
changes in the order. So, I'm not sure where to go from here?!?!

Thanks... if you happened to think of another solution, I truly would
appreciate additional pointers.

Again, thanks!

Tom
 
Back
Top