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
Error.png
surah79Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
surah,

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"
0
 
Rey Obrero (Capricorn1)Commented:
0
 
surah79Author Commented:
Hi

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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db
0
 
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"

thanks
One-Deal-Register.mdb
0
 
ggzfabConnect With a Mentor Commented:
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.
0
 
surah79Author Commented:
hi Fyed

I tried but still not working on the report
0
 
Dale FyeCommented:
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.
0
 
surah79Author Commented:
Thanks a million  it working now, by both the ways.
0
 
Dale FyeCommented:
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.
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.