Query - Running Total Help

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

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
 
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
 
Back
Top