Combine two queries

Dear all,

I have two queries that count the number of Hours each field is over 24 (first query) and 30 (second query).  I would like to combine them into one query.  

SELECT Count(Dataset.Hour) AS Count_24
FROM Dataset
WHERE (((Dataset.[Field 1])>24) OR ((Dataset.[Field 2])>24)));

Open in new window


SELECT Count(Dataset.Hour) AS Count_30
FROM Dataset
WHERE (((Dataset.[Field 1])>30) OR ((Dataset.[Field 2])>30)));

Open in new window


Thanks
AndyC1000Asked:
Who is Participating?
 
Dale FyeCommented:
These two criteria are not exclusive.  As long as you understand that, then the following should count the # of records where [Field 1] or [Field 2] > 24 and then > 30.  However, I do not see where Count_30 could be > Count_24, since it is actually a subset of the Count_24.

SELECT SUM(IIF(([Field 1] > 24) OR ([Field 2] > 24), 1, 0)) as Count_24
, Sum(IIF(([Field 1] > 30) OR ([Field 2] > 30), 1, 0)) as Count_30
FROM Dataset

IF you want >24 and < 30 for Count_24, that syntax would look like:

SELECT Sum(IIF(([Field 1] > 24 AND [Field 1] < 30) OR
                             ([Field 2] > 24 and [Field 2] < 30), 1, 0)) as Count_24
, SUM(IIF(([Field 1] > 30) OR ([Field 2] > 30), 1, 0)) as Count_30
FROM Dataset
0
 
Rey Obrero (Capricorn1)Commented:
try using a Union query

SELECT Count(Dataset.Hour) AS Count_Hours
FROM Dataset
WHERE (((Dataset.[Field 1])>24) OR ((Dataset.[Field 2])>24)))
UNION ALL
SELECT Count(Dataset.Hour) AS Count_Hours
FROM Dataset
WHERE (((Dataset.[Field 1])>30) OR ((Dataset.[Field 2])>30)))
0
 
AndyC1000Author Commented:
I tried the union all query, it produced the results under one field Count_Hours.  

I require the results in the following format.  I should have made this clearer in the question.

Count_24   Count_30
12               56
0
 
Rey Obrero (Capricorn1)Commented:
try this

Select Sum(IIF([Field 1] >24 OR [Field 2]>24, 1,0) as Count_24, Sum(IIF([Field 1] >30 OR [Field 2]>30, 1,0) as Count_30
from Dataset
0
 
Rey Obrero (Capricorn1)Commented:
missing ")"

Select Sum(IIF([Field 1] >24 OR [Field 2]>24, 1,0)) as Count_24, Sum(IIF([Field 1] >30 OR [Field 2]>30, 1,0)) as Count_30
 from Dataset
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.