Combining queries

[Sheba]

Hi,

I have these two columns in my table. I want my query to tell me how many records have oxygen indicated and also, how many of the OxyIndic instances were given oxygen. According to my table below, the answers should be 4 and 3. I created the queries:

Query1
SELECT Sum(IIf(OxyIndic="Yes",1,0)) AS Oxygen_Indication
from table1; and when I run it separately, it gives me 4

Query2
SELECT Sum(IIf(O2Indic="Yes",1,0)) AS HowMany
FROM table1
WHERE O2Given="Yes";


when I run them separately, they give me 4 and 3 respectively but when I try to combine both queries I get output 3 and 3. Could someone advise on how to fix this please? Your help is much appreciated

table1

OxyIndic	OxyGiven
Yes	Yes
Yes	No
Yes	Yes
Yes	Yes
No	Yes

[JoeM]

How about:

SELECT Sum(IIf(OxyIndic="Yes",1,0)) AS Oxygen_Indication, Sum(IIf((OxyIndic="Yes") And (O2Indic="Yes"),1,0)) AS HowMany
FROM table1;

[Sheba]

okay so I used UNION. It is giving me the correct figures only it is listing them under one column. I want them in separate columns

[JoeM]

You probably missed my reply above in passing. Try that out.

[Sheba]

Hi JoeM, thanks for your response. I tried your SQL code but it asked me to "Enter Parameter Value"...

[Sheba]

what is wrong with this code? It is giving the correct values but listing them under one column... I want them in two columns

SELECT Sum(IIf([u].[oxyIndic]="Yes",1,0)) AS Oxygen_Indication FROM table1 u
UNION
SELECT Sum(IIf([t].[oxyIndic]"Yes",1,0)) AS HowMany FROM table1 t
WHERE [t].[oxyGiven]="Yes";

[JoeM]

There seems to be an issue with the fields used in Query2 in your original post (i.e. field names are different than what you are showing in your table).
Try changing "O2Ind" to "OxyGiven" in the SQL code I gave you (make sure those two field names match whatever field names you are really using).

[JoeM]

what is wrong with this code? It is giving the correct values but listing them under one column... I want them in two columns

Union Queries do not add fields (columns), they add records (rows).

If you clean up your field names, the code I gave you should work (see previous reply).

[Sheba]

It worked. Very much appreciated.