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

sum field twice in same query using different criteria

aas4mis
P: 97
I have a table stating descriptions (desc) and quantities (qq). How would I go about getting the sum for all rugs [desc like "*rug*"] and the sum for all non rugs [desc not like "*rug*"] in the same query/subquery?
Thanks in advance. This has been pushing my buttons for the last hour and a half.
Aug 4 '08 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You can use two calculated fields using IIFs as shown below:

Expand|Select|Wrap|Line Numbers
  1. Select desc, sum(IIF(desc like "*rug*", [qq], 0)) as sum1, sum(IIF(desc not like "*rug*", [qq], 0) from Yourtable Group By Desc ... etc 
-Stewart
Aug 4 '08 #2

Expert Mod 2.5K+
P: 2,545
Sorry, when I wrote the above reply I included the Desc field in the Select. You should NOT include it in your query, as to do so will stop the two computed fields from grouping on the partial matches which you need - just include the two computed fields on their own.

-Stewart
Aug 5 '08 #3

aas4mis
P: 97
Hi. You can use two calculated fields using IIFs as shown below:

Expand|Select|Wrap|Line Numbers
  1. Select desc, sum(IIF(desc like "*rug*", [qq], 0)) as sum1, sum(IIF(desc not like "*rug*", [qq], 0) from Yourtable Group By Desc ... etc 
-Stewart
Beautiful. Thanks for the help, just what I was looking for. I was even able to reference both aliases and divide to get the percentage of rugs skipped.

** don't forget the ")" after the second IIF statement.
Aug 5 '08 #4

NeoPa
Expert Mod 15k+
P: 31,308
...
** don't forget the ")" after the second IIF statement.
You just can't get the staff nowadays :D
Aug 10 '08 #5

aas4mis
P: 97
You just can't get the staff nowadays :D
Ha! I think they do a fine job here on the access forum, from my experience this is the most active. Oh yeah.. good job with the new [C0DE] fields.. looks good.
Sep 20 '08 #6

Post your reply

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