Ralph Finianos
asked on
Running Sum in Query
hello,
i have a table named
Transaction:
ID Primary Key
Entry Date
T name
T amount
i need the running Sum of the amount Over T name can any one advise how to make it ?
i have a table named
Transaction:
ID Primary Key
Entry Date
T name
T amount
i need the running Sum of the amount Over T name can any one advise how to make it ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You might want to try my method as well, depending on the size of your recordset, the embedded subquery method recommended by Rey can take substantially longer.
Dale
Dale
In a report, you can use the RunningSum property of a textbox to sum over a group or all records.
If you want to display it in a form, and the EntryDate field contains both date and time values, you can create a query that looks something like:
SELECT T1.EntryDate, T1.Amount, Sum(T2.Amount)
FROM yourTable as T1
LEFT JOIN yourTable as T2 ON T2.EntryDate <= T1.EntryDate
GROUP BY T1.EntryDate, T1.Amount
If the EntryDate field does not contain time, but only date, you could probably include the TransactionID column (which is probably an Autonumber, but does not guarantee that each record will be increasing), which is why I would not generally use it alone.
SELECT T1.EntryDate, T1.TransactionID, T1.Amount, Sum(T2.Amount) as Running Sum
FROM yourTable as T1
LEFT JOIN yourTable as T2 ON T2.EntryDate <= T1.EntryDate
WHERE T1.TransactionID <> T2.TransactionID
GROUP BY T1.EntryDate, T1.TransactionID, T1.Amount