getting rid of zeros in the output of an access query

Question

i have a query in access that is this:

SELECT iif([Cup Type] like '*Cylinder*',count([Cup Type]),0) AS Cylinder, 
iif([Cup Type] like '*Snap*',count([Cup Type]),0) AS Snap, 
iif([Cup Type] like '*Tip*',count([Cup Type]),0) AS Tip, 
iif([Cup Type] like '*Other*',count([Cup Type]),0) AS Other
FROM [Lab Occurrence Form]
WHERE [1 0 Preanalytical (Before Testing)] Like '*1.3 QNS-Quantity Not Sufficient*' 
And ((([Lab Occurrence Form].[Occurrence Date]) 
Between Forms!Meeting_Reasons_Frequency!Text4 
And Forms!Meeting_Reasons_Frequency!Text2))
GROUP BY [Cup Type];

the output looks like this:

Cylinder    Snap    Tip Other
0   0   0   0
71  0   0   0
0   0   0   18
0   7   0   0
0   0   4   0

i apologize for the horrific formatting, but how do i get rid fo the zeros???

should i do something like select cylinder, snap, tip, other, from query1 where cylinder <> 0 etc..???

i want the output to be:

Cylinder    Snap    Tip Other
71  7   4   18

Answer 1

Do you mean you want to get rid of the row with all zeroes? Then you would have to add something like

AND ([Cup Type] like '*Cylinder*' or [Cup Type] like '*Snap*' or [Cup Type] like '*Tip*' or [Cup Type] like '*Other*')

to your where condition.

Answer 2

After your clarification, what you would want, is switch the order of the count() and IIf() and change it to sum() so that you get

sum(iif([Cup Type] like '*Cylinder*', 1, 0))

etc.

and then remove the group by in order to get one result row only.

Answer 3

It's not clear what you'd be grouping on, but you could use a Max() on each of the fields.

But I suspect the issue is more complicated than your explanation suggests.