hello guys..

can anyone tell me what is the proper query of display item that i want..
i want to display the # of available,sold,used,defective items in every shipment.


Shipment | Available | Sold | Used | Defective
---------+-----------+------+------+----------+


i tried this query but not displaying the expected result..


Code:
rs.Open "Select DISTINCT [shipment],(Select count([status]) from item where [status]='available') as Available," & _
                          "(Select count([status]) from item where [status]='sold') as Sold," & _
                          "(Select count([status]) from item where [status]='used') as Used," & _
                          "(Select count([status]) from item where [status]='defective') as Defective" & _
                          " from item", cn, adOpenDynamic, adLockOptimistic
thnk you...

note: all fields are in the same table..