asked on

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

Rey Obrero (Capricorn1)

see this link

How to create a running totals query in Access

surah79

ASKER

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

Rey Obrero (Capricorn1)

upload a copy of the db

surah79

ASKER

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

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ggzfab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

surah79

ASKER

hi Fyed

I tried but still not working on the report

Dale Fye

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.

surah79

ASKER

Thanks a million it working now, by both the ways.

Dale Fye

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.