By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,819 Members | 1,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,819 IT Pros & Developers. It's quick & easy.

Access DSUM on Multiple Criteria

P: 7
I'm trying to do a running sum in access based on two different criteria.

I have an ID, Quarter, and TotalAmount. For each ID and Quarter combination I want to see the running total (e.g. Claim ID 1 for Q1 and Q2 with Q2 being the sum of Q1 and Q2 values)

I've tried a bunch of different codes, but nothing is working. Here's what I have right now:

Expand|Select|Wrap|Line Numbers
  1. =DSum([TotalAmount],"groupByQuarter","[ID] = " & [ID] & AND [Quarter]>= '" & [Quarter])
6 Days Ago #1
Share this Question
Share on Google+
15 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,278
gdonn3008,

Welcome to Bytes!

I think you almost have it:

Expand|Select|Wrap|Line Numbers
  1. =DSum([TotalAmount],"groupByQuarter","[ID] = " & [ID] & " AND [Quarter]<= '" & [Quarter] & "'")
I just changed the ">=" to "<=".

However, this will cause problems if you have multiple years going on.
5 Days Ago #2

P: 7
Yes, there are multiple years but in format YYYYQ and formatted as a numberfield.

The below works when just using ID criteria but when I add the AND statement the first Qrtr field is getting syntax error.

Expand|Select|Wrap|Line Numbers
  1. DSum("[TotalAmount]","groupByQuarter","[ID]='" & [ID] & "'" AND "[Qrtr]<=" & [Qrtr] )
5 Days Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,278
Your "AND" is missplaced outside the quotes. Try copy and paste of my code snippet.
5 Days Ago #4

P: 7
Just tried that and got the error below:

"You tried to execute a query that does not include the specified expression 'Dsum.........' as part of an aggreagate function
5 Days Ago #5

P: 7
The issue seems to be in joining the two statements because I've also tested the below statement and this works correctly:
Expand|Select|Wrap|Line Numbers
  1. Running Total: DSum("[TotalAmount]","groupByQuarter","[Qrtr]<=" & [Qrtr] & "")
5 Days Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,278
Is your ID a number or a text value?

If it is a numerical value, the DSum() should work exactly as I described.
5 Days Ago #7

P: 7
It's a text value as it is a mix of Letter and Numbers
5 Days Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,278
Try this:

Expand|Select|Wrap|Line Numbers
  1. =DSum([TotalAmount],"groupByQuarter","[ID] = '" & [ID] & "' AND [Quarter]<= '" & [Quarter] & "'")
5 Days Ago #9

P: 7
Hmm.. still not working. Getting the error "You tried to execute a query that does not include the specified expression 'Dsum.........' as part of an aggreagate function" again.


I also tried removing the ' quotes around Quarter (below) since Quarter is numeric YYYYQ. In this version I am getting the error "The expression you entered has an invalid string. A string can be up to 2048 characters long, including opening and closing quotation marks."
Expand|Select|Wrap|Line Numbers
  1. DSum([TotalAmount],"groupByQuarter","[ID] = '" & [ID] & "' AND [Quarter]<= " & [Quarter] & ")
5 Days Ago #10

twinnyfo
Expert Mod 2.5K+
P: 3,278
Try removing he final ď&Ē and the closing quotes.

Is this in a query or a control source on a form/report? This isnít making any sense.
5 Days Ago #11

P: 7
I used query design, added the table and then am writing this as an expression in the one field.

BTW I truly appreciate all of your help. Unfortunately removing the final & and closing quotes did not work either.
5 Days Ago #12

twinnyfo
Expert Mod 2.5K+
P: 3,278
Iíll send a note on this in the morning. But your answer will be to use a Sum(), not a DSum(), but use the criteria as to whether you will sum it or not. Iíll explain in the morning. Knowing that this is a query and not a control source is more helpful than you can imagine.

If you donít mind posting your query (at least the parts that work) - that would also be helpful.
5 Days Ago #13

twinnyfo
Expert Mod 2.5K+
P: 3,278
I think what you want is something like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     ID, 
  3.     Left([Quarter], 4) AS EarnYear, 
  4.     Quarter AS Qtr, 
  5.     Sum([TotalAmount]) AS QuarterEarn, 
  6.     Sum(IIf([Quarter] < = [Qtr], [TotalAmount], 0) AS YTDEarn 
  7. FROM 
  8.     YourTableName 
  9. GROUP BY 
  10.     ID, 
  11.     Left([Quarter], 4) AS EarnYear, 
  12.     Quarter AS Qtr 
  13. ORDER BY 
  14.     ID, 
  15.     Left([Quarter], 4) AS EarnYear, 
  16.     Quarter AS Qtr;
Please note, that I am assuming that "groupBy Quarter" is an aggregate query you have been using to add things by Quarter? So, the field "TotalAmount" would have to be the amount from your original Table.

Also, the Querter Field, if it is just a Date in the original Table, will have to be dealt with in a way to get the Year and Quarter. For example, if all you have in your table is a date (with no Year or quarter, you can do something like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     ID, 
  3.     Year([TransactionDate]) as EarnYear, 
  4.     IIf(Month([TransactionDate]) < = 3, 1, IIf(Month([TransactionDate]) > 3 And Month([TransactionDate]) <= 6, 2, IIf(Month([TransactionDate]) > 6 And Month([TransactionDate]) <= 9, 3, 4))) AS Qtr, 
  5.     Sum([Earn]) AS QuarterEarn, 
  6.     Sum(IIf(IIf(Month([TransactionDate]) < = 3, 1, IIf(Month([TransactionDate]) > 3 And Month([TransactionDate]) <= 6, 2, IIf(Month([TransactionDate]) > 6 And Month([TransactionDate]) <= 9, 3, 4))) < = [Qtr], [TotalAmount], 0) AS YTDEarn 
  7.    etc.
Yes, that is very messy, but in theory, I think it would work.

Hope this hepps!
4 Days Ago #14

Rabbit
Expert Mod 10K+
P: 12,364
This error: "You tried to execute a query that does not include the specified expression 'xxx' as part of an aggregate function" is the result of using an aggregate query without putting all fields either in an aggregate function or in a group by. DSum is not an aggregate function in the SQL sense.

Either put it in the group by or put an aggregate function around it. Or, if you have no need for an aggregate query in the first place, change the query back to a regular select query.

Alternatively, instead of a DSum, you can achieve the same result by joining the table to itself so you can calculate a running sum.
4 Days Ago #15

NeoPa
Expert Mod 15k+
P: 31,470
These issues are very often the result of getting the syntax wrong.

On the other hand there have been alternatives posted that may well be a much better approach anyway. I suggest you try them out and let us know how they work for you.

If you still need help after that then be very clear about which approach you'd like to pursue and post again the code you're using at the point you want help from.

Remember, Copy & Paste is critical. Much time and effort is frequently wasted chasing down blind alleys due to members typing stuff into the thread with transposal errors. No-one ends up happy when Copy/Paste avoids that possibility and is so easy. Our time is free. That doesn't mean it's without value. NB. That is not a criticism of you. Apart from the [CODE] tags issue you've been perfect so far.

Use the [CODE] when posting code or data. This has been pointed out to you on numerous occasions but somehow doesn't seem to have got through yet. Save our time having to do it for you again by remembering to do that when you post. Ask for help if you need it but look first when you post. It's really quite straightforward if only you know to look. You're new. No drama. Let's just see if we can get it right going forward.

As a last point, and in an effort to be some actual practical help in this particular instance, let me draw your attention to what Twinny says in post #13. It's a short post but very apposite.

On the other hand, we tend to help people jump forward to better approaches where we can but sometimes, especially for those very new to databases or coding, the member will prefer simply to get direct help on exactly what they're trying to do. They understand that to the extent they can and feel a bit out of their depth when moved on to alternative approaches. We will be happy to help either way. Just let us know clearly. There's time enough for progress as and when members are ready.

Good luck and I hope you enjoy working in Access as much as we all do.
3 Days Ago #16

Post your reply

Sign in to post your reply or Sign up for a free account.