count query problem

[dolano]

This is a frustrating problem, I want to count all entries in a field that are "N" but instead of displaying the total count ie 120 i want it to show 0 until "N" is replaced by "Y". what I have got so far is

SELECT [CABLE DATA QUERY].TOP, Count([CABLE DATA QUERY].[Cable Test]) AS [CountOfCable Test]
FROM [CABLE DATA QUERY]
WHERE ((([CABLE DATA QUERY].[Cable Test]) Not Like "y"))
GROUP BY [CABLE DATA QUERY].TOP;

I know this will count everything in the field that is not Y and return the count of those values however is there a way of getting an inverse count.

 

[namliam]

SELECT [CABLE DATA QUERY].TOP, Count([CABLE DATA QUERY].[Cable Test]) AS [CountOfCable Test]
FROM [CABLE DATA QUERY]
WHERE ((([CABLE DATA QUERY].[Cable Test]) Not Like "N"))
GROUP BY [CABLE DATA QUERY].TOP;

?? Will that do??

 

[dolano]

tried

Thanks for that, had already tried that. what is happening is one of two things, a) a return of the count of all values in the field (thats what it should do) or
b) if i change the criteria to count items not in the field returns nothing (once again doing what it should be)

The result I am trying to achieve is instead of returning the count if some how it could return a zero where the criteria is not like "Y"

 

[namliam]

SELECT [CABLE DATA QUERY].TOP, sum(iif([CABLE DATA QUERY].[Cable Test]="Y",1,0) AS [CountOfCable Test]
FROM [CABLE DATA QUERY]
GROUP BY [CABLE DATA QUERY].TOP;

If that not it, can you post some sample data and how you would want your answer to look?