Create single table using multiple criteria

[wholeyrood]

Hi,
I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.

My table is in the form,
TimeandDate,WS127m_Avg,WS82m_Avg....

I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,
SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s
FROM CL_AllData
WHERE (((WS127m_Avg)=9999))
GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);

I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).

Any pointers gratefully received!

 

[plog]

First, I wouldn't call a field (even within a query) 'Month'. You should rename it to 'ReportMonth' or prefix it with something related to the data. 'Month' is a reserved word and it can cause issues when writing queries or code for it.

For your issue, you need to move your criteria from the WHERE clause to a new field within the SELCT clause like so:

SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS ReportMonth, Sum(iif(WS127m_Avg]=9999, 1, 0)) AS WS127_9999s, Sum(iif(WS82m_Avg]=9999, 1, 0)) AS WS82_9999s
FROM CL_AllData
GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);

I changed the Count() to a Sum() to accomodate this. If the record meets the criteria it shows a 1, if not it shows a 0, then you just add those 1 and 0's up to get the total records that meet the criteria.

 

[wholeyrood]

Hi plog,

Thanks a lot - this works perfectly! Much appreciated!