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 ?
Ralph FinianosAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
test this

select id, [Entry Date],[T name],[T amount],(select sum(X.[T amount]) from Transaction as X where X.[T name]=Transaction.[T name] and X.[Entry Date]<= Transaction.[Entry Date]) as RunningTotal
from Transaction
group by id, [Entry Date],[T name],[T amount]
Order by [T name], [Entry Date]
1
 
Dale FyeCommented:
how do you intend to display this, in a form, or a report?

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
0
 
Dale FyeCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.