Counting text type codes

  • Thread starter Thread starter teach1
  • Start date Start date
T

teach1

Help!!

I have just started to stock shelves with makeup products at local outlets.
I need to keep a track of the product range within each shop.

I have created a table in Access 2003 containing Shop ID, Product 1 to
Product 10.
For each Product I will enter the following codes:
S- In Stock;
O – On Order
N- No Stock
Empty – Where I have no info yet.

I need to be able to Count the no of codes for each Product. I really need
a Countif type statement which I can do in Excel but doesn’t seem to be in
Access.

Please help, I am fairly new to Access advance query!

Thanks in advance.
 
SELECT Product,
Sum(IIf([Status] = "S", 1, 0)) AS InStock,
Sum(IIf([Status] = "O", 1, 0)) AS OnOrder,
Sum(IIf([Status] = "N", 1, 0)) AS NoOrder,
Sum(IIf([Status] = "", 1, 0)) AS NoInfo
FROM MyTable
GROUP BY Product
ORDER BY Product
 
Back
Top