Access Query running sum

Hi guys
I am trying to write a running sum query but its giving an error.
SELECT [qryCPE's New Increase Refinance no of Transaction].Month, Sum([qryCPE's New Increase Refinance no of Transaction].[Total Transactions]) AS [SumOfTotal Transactions1], MonthText2Number([T1]) AS MO, Left([Month],Len([Month])-3) AS T1, DSum("[SumOfTotal Transactions1]","[qryCPE's New Increase Refinance no of Transaction]","MO <=" & [MO]) AS test
FROM [qryCPE's New Increase Refinance no of Transaction]
GROUP BY [qryCPE's New Increase Refinance no of Transaction].Month;

Open in new window

It should give the below result
Month      SumOfTotal      MO      Test/Running Sum
Mar-13      114                        3                        114
Apr-13      124                        4                         238
May-13      117                        5                         355
Jun-13      74                        6                          429
Jul-13      92                        7                          521
Aug-13      25                        8                          546
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
surah79Author Commented:

I tried this link already as still I am not able to figure why its giving me the error .

May be is due to formatting issue  not sure still
Rey Obrero (Capricorn1)Commented:
upload a copy of the db
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

surah79Author Commented:
Hi Cap

Below is the query name On which I am trying to do a running total

"qryCPE's New Increase Refinance no of Transaction Query"

Dale FyeOwner, Developing Solutions LLCCommented:

My first question is where do you plan on using this query?  If it is in a report,  get rid of the extra calculation.  Add a control to your report for this value, set it's control source to [SumOfTotal] and then set its  "Running Sum" property (on the data tab) to "Over Group"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guess you need to use for the DSUM:

DSum("[SumOfTotal Transactions]","[qryCPE's New Increase Refinance no of Transaction]","MO <=" & [MO])

Open in new window

as the original fieldname is [SumOfTotal Transactions] and not the "AS"  name from your query.

Also be prepared that this query will take some time, as the DSUM is rather sloooow....

I would use fyed's proposed running sum in a report when you just need the output.
surah79Author Commented:
hi Fyed

I tried but still not working on the report
Dale FyeOwner, Developing Solutions LLCCommented:
Can you post the SQL string you were using as the RecordSource for the report.  I'm unable to download your mdb do to company policy.
surah79Author Commented:
Thanks a million  it working now, by both the ways.
Dale FyeOwner, Developing Solutions LLCCommented:
If you are only using this in the report, then it will be much quicker if you use the RunningSum method rather than the query method.

However, if you expect to use this in a form (continuous or datasheet), then you will have to do it in a query.  The key to doing it in the query is to make sure your sort order is the way it is being presented in the query and the form.  If a user sorts your continuous form or datasheet differently than it is calculated in the query, your running sum will not make sense.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.