RunTotal

[rob p]

I want to add a RunTotal to a query. Table is tbltest.
tstname tstcode tstamt
aaa 01 100.00
aaa 02 50.00
bbb 01 25.00
ccc 03 100.00
ddd 01 50.00


Groups are by tstname but I want to have a running total for codes 01 and
02. I put in a column in query and called it an expression but can't get it
to work.
thanks.

 

[Jeff Boyce]

Rob

Sounds like you're describing a report. Why not use the query you already
have, group by tstname, and add a control that holds "=Sum([YourControl])"?

Good luck

Jeff Boyce
<Access MVP>

 

[Tom Ellison]

Dear Rob:

SELECT tstname, tstcode, tstamt,
(SELECT SUM(tstamt) FROM tbltest T2
WHERE T2.tstname = T1.tstname
AND T2.tstcode <= T1.tstcode)
FROM tbltest T1
ORDER BY tstname, tstcode

This uses a correlated subquery and aliasing (potential study topics
to understand how this works.

If you have duplicate tstcode for a tstname the order between them is
ambiguous, and both will be added into the running sum simultaneously.
If this is the case, and is undesirable, you must supply additional
columns by which to sort and sum so that it is no longer ambiguous.

I want to add a RunTotal to a query. Table is tbltest.
tstname tstcode tstamt
aaa 01 100.00
aaa 02 50.00
bbb 01 25.00
ccc 03 100.00
ddd 01 50.00


Groups are by tstname but I want to have a running total for codes 01 and
02. I put in a column in query and called it an expression but can't get it
to work.
thanks.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts