Query - Running Total Help

[Joe Williams]

Hello,

Looking to do a running total in a query, whose base table looks like this:

EMPLOYEEID, TRANSACTIONDATE, AMOUNT
1234, 1/1/2009, $50
1234, 1/1/2009, $10
5555, 2/1/2009, $20
5555, 3/1/2009, $25

I would like the resultant query to display the data as follows:

EMPLOYEEID, TRANSACTIONDATE, AMOUNT, RUNNINGTOTAL
1234, 1/1/2009, $50, $50
1234, 1/1/2009, $10, $60
5555, 2/1/2009, $20, $20
5555, 3/1/2009, $25, $45

So basically the running total resets when the employeeID changes. I have
tried various DSUM functions but have not had any success thus far. Any help
you can provide would be appreciated. Thanks in advance.

Joe

 

[Ron Weiner]

If your table name was named tblRunningTotal then the query below ought to
get you going

SELECT tblRunningTotal.EMPLOYEEID, tblRunningTotal.TRANSACTIONDATE,
tblRunningTotal.AMOUNT,
(Select Sum(Amount) from tblRunningTotal as RT Where rt.Employeeid=
tblRunningTotal.Employeeid and rt.TransactionDate
<=tblRunningTotal.transactiondate) AS xx
FROM tblRunningTotal
ORDER BY tblRunningTotal.TRANSACTIONDATE;

Returns
EMPLOYEEID TRANSACTIONDATE AMOUNT xx
1234 1/1/2009 $50.00 $50.00
1234 1/2/2009 $10.00 $60.00
5555 2/1/2009 $20.00 $20.00
5555 3/1/2009 $25.00 $45.00


Ron W