Query where count of a value is 0

[nigelbloomy]

I am trying to get a query where I can pull all of the line items that don't have a certain value.
I have an item code and for each item code I have several instances of the letter "P" in a field [Acquired] for each time the item was purchased. Sometimes we can get the item through another method, so that same field might have an "S" or maybe even just be blank. I am trying to put together something to just get the items that have never been purchased, ie there are no "P"s in the Acquired field in all the history for the item number. I keep trying to use count, but I can't figure out how to conditionally count just the P's, or how to filter out just the ones that have a 0 count for the P's.

Any ideas?

[June7]

Do you have a table of all possible item codes?

SELECT * FROM ItemCodes WHERE NOT ItemCode IN (SELECT ItemCode FROM Purchases WHERE Acquired="P");

[nigelbloomy]

That's it. I hadn't thought of using anything like that, but it makes sense.

Thank you so much!