Avatar of Liberty4all
Flag for United States of America

asked on 

How to create a running sum in an Access query

I want to create a running sum in an Access query that calculates daily cumulative totals.  The fields I'm using are Date, Month, Year, and Total.  The Total field (column) contains the daily account balance changes (positive or negative) for each day, and these are to be reflected in the running sum total.  The table name is tblAcctBalAllFunds_CFYTD.

I've tried the following Dsum statements which produce either blank or incorrect results so I'm guessing I don't have the syntax correct.

DSum("Total","tblAcctBalAllFunds_CFYTD","Date<=" & [Date]) - produces blank results

DSum("Total","tblAcctBalAllFunds_CFYTD","Date<=" & [Adate] & "") - produces blank results (Adate is an alias for Date)

DSum("Total","tblAcctBalAllFunds_CFYTD","DatePart('m', [Date])<=" & [AMonth] & " And DatePart('yyyy', [Date])<=" & [AYear] & "") - produces incorrect results (AMonth and AYear are aliases for Month and Year)

Any suggestions on what to try next would be appreciated.
Microsoft AccessSQLPower BI

Avatar of undefined
Last Comment

8/22/2022 - Mon