How to show who DIDN'T buy a particular item.

[mindea]

Seems like this simple scenario would have been covered a zillion times (and probably has) but my searches have not come up with an answer. (Maybe I need help doing web searches!)

Here's my [grossly simplified] issue. I have a table that stores purchase history -- that is, who bought what color balloons.

Jack - red
Jack - blue
Jack - yellow
Lane - blue
Lane - yellow
Lane - green
Fred - red
Fred - blue


Fred - yellow

The boss wants to know who didn't buy any red balloons. He'd also like to know who didn't buy any green balloons.

Can someone point out how I would go about this?
Thanks.

[June7]

SELECT * FROM people WHERE peopleID NOT IN (SELECT peopleID FROM purchases WHERE color = 'red");

Do the same for green.

[mindea]

Thank you for that June7. I've been away from Access for a bit and completely forgot about the NOT IN statement.

I've tried your suggestion on my actual tables and it seems to be taking forever to run.
The NOT IN select statement (which I had to make SELECT DISTINCT to get unique peopleIDs) returns 14 records.
My actual Purchases table contains 130,000 records.
Like I say, it just runs without ever showing a result.
I quickly created a table (tbl_NotRed_peopleID) with those 14 peopleIDs and then made the NOT IN statement: NOT IN (SELECT peopleID FROM tbl_NotRed_peopleID); and that works fine.
Could it be the SELECT DISTINCT in the NOT IN statement is causing the problem?

[mindea]

Gak! Got it working now. I unintentionally had the outer SELECT section with SELECT DISTINCT. Now it's working. Many thanks again for your help.

[June7]

Don't really need DISTINCT.