473,378 Members | 1,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

sum field twice in same query using different criteria

aas4mis
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
5 4392
Stewart Ross
2,545 Expert Mod 2GB
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
Stewart Ross
2,545 Expert Mod 2GB
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
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
32,556 Expert Mod 16PB
...
** don't forget the ")" after the second IIF statement.
You just can't get the staff nowadays :D
Aug 10 '08 #5
aas4mis
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

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

Similar topics

8
by: mark | last post by:
Access2000 How do I write a query that combines the CTC field from each record below into one record? I need to concatenate the CTC field with a separator, like below: ...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
2
by: Megan | last post by:
Hi everybody- I have 2 tables, Hearings and Rulings. Both have primary keys called, CaseID, that are autonumbers. I don't want both tables to have the same autonumber. For example, if Hearings...
1
by: Ivan Carey | last post by:
How can a query display multiple fields with diferent condition on the same field example I have a field name of reason and a field name of duration. I would like to display 2 fileds of total...
0
by: MLH | last post by:
I have an A97 query (qryVehiclesNowners2) that has a table field in it named . Depending on the selections made in a number of criteria choices on a form, a field on the form will have string...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
9
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
2
by: Jimmy Stewart | last post by:
Ok, I'm trying to write a query that is starting to wear me down. What I'm trying to do is create a year end report that gets sent to all of my customers who meet two criteria. One they are...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.