help with access query

[E_Gold]

i need to count where the qty>0 and where the qty=0 like this:

i have this table

Serial | Qty | NewQty

==============

700 | 1.3 | 0

700 | 4.2 | 4

700 | 9.3 | 0

700 | 5.5 | 5.3

900 | 1.1 | 0

900 | 2.2 | 5.3


i need to see this

Serial | count1 | count2

===============

700 | 4 | 2

900 | 2 | 1



how to do it ?

thanks in advance

 

[plog]

Your initial statement and your example data conflict. There is no mention of using the field called 'NewQty' in your initial sentence, but you used it determine count2 in your example.

This is the SQL you would need based on your example data:

SELECT Serial, SUM(IIF(Qty>0, 1,0)) AS count1, SUM(IIF(NewQty=0, 1,0)) AS count2 FROM YourTableNameHere GROUP BY Serial;

To achieve what you described in your initial sentence, replace "NewQty" with "Qty".

 

[E_Gold]

Thanks !!!! its work !!!!