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>

 

[Pettor]

I made it easier as I wanted to count only "YES". Let me check it and I will revert.

 

[xenou]

repairing the other query:

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

Reporting only groups with at least one yes answer:

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

I think that would work - untested.

Or:

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

 

[Pettor]

Excellent, It is working just fine!!

 

[xenou]

Good.
And I made one mistake:

Sum(IIf([2P_TABLE].[TWO] = "-1", 1, 0)) AS ycount

should have been:

Sum(IIf([2P_TABLE].[TWO] = -1, 1, 0)) AS ycount