MS ACCESS 2016, Count IIF issue

[Pettor]

Hi All,

I have a table with two columns like the below and I want to write a query that will count the YES values grouping by the A column. Any suggestions?
I tried Count(IIf()) and DCOUNT functions but none of them worked...

1	YES
2	YES
3	YES
3
4	YES
5	NO
5	YES
5	YES

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

1	1
2	1
3	1
4	1
5	2

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Something like:

SELECT Table1.x, Sum(IIf([y]="yes",1,0)) AS ycount
FROM Table1
GROUP BY Table1.x;

where x and y are the relevant fields with x consisting of numbers and y = yes, no, or null.

 

[Pettor]

As it is still not working can you please find the mistake here?

SELECT [2P_TABLE].[ONE], Sum(IIf([2P_TABLE].[TWO]="YES",1,0)) AS ycount
FROM [2P_TABLE]
GROUP BY [2P_TABLE].[ONE];

I receive the message: Data type mismatch in criteria expression. I don't know why.

 

[xenou]

Should work:

SELECT
    [Field1], 
    Count([Field1]) as Total
FROM 
    TABLE1
WHERE 
    [Field2] = 'YES'
GROUP BY [Field1]

But probably would have no records if there were only 'No' in any case (or no Yesses at all, anyhow)

 

[xenou]

The query you posted works for me, BTW. Are sure these are text fields with text values for "YES" and "NO"?

 

[Pettor]

That is the problem, I want it to show also those that have the "no" as value.

 

[xenou]

Make sure your test data is complete with all the types of results you want to see.

 

[xenou]

The query you posted works for me, BTW. Are sure these are text fields with text values for "YES" and "NO"?

Again, check the data types...

 

[Pettor]

The data type is a YES/NO field with a default value of "No". Isn't it correct?
When I tick the box it gets the "Yes" value and if not it gets the "No".
Am I missing something?

 

[xenou]

A Yes/No field is not text.
Yes = -1
No = 0

so you can just sum then I guess:

SELECT 
	[2P_TABLE].[ONE], 
	SUM(-[2P_TABLE].[TWO]) AS ycount
FROM [2P_TABLE]
GROUP BY [2P_TABLE].[ONE];

Npte: Are you sure there is a default value of "No" (zero). Your test data shows a blank!!