Running Sum

[rpgowned]

I would like to do a query with a running sum.. It has been over a decade since i worked in access and I can't for the life of me figure out how to do it

Table name Transactions
Fields: ID, Date,Number,Transaction.Account,Change,Cleared



ID is a PK
I need to have the change field summed on each transaction line as Balance
negative amounts are in parentheses

if anyone could script this query quick for me would be greatly appreciated

Nevermind I figured it out

SELECT tbl_Alias.ID, tbl_Alias.Date, tbl_Alias.Number, tbl_Alias.Transaction, tbl_Alias.Account, tbl_Alias.Change, (SELECT Sum(Transactions.Change) AS Balance
FROM Transactions
WHERE (((Transactions.ID)<=[tbl_Alias].ID)) AS Balance, tbl_Alias.Cleared
FROM Transactions AS tbl_Alias;

[gopherking]

rpgowned,

I had this same question a while ago, but there is no easy answer for doing a running sum in a Query (or at least I haven't received it yet either), but I did come across the following which explains how you can set it up (I didn't bother due to the complexity and I didn't need it that bad; I just used a Report to do it for me since that was where I needed the running sum anyway):

http://msaccesstips.com/2010/01/auto...-query-column/

If it helps, glad I could contribute!

[pbaldy]

A running sum is typically done either with a subquery or a DSum(). Either one would have a criteria that summed all previous transactions including the current one. That would involve the date field, and perhaps another field if there could be more than one transaction on a date. Searching on "running sum" should turn up lots of examples.