Access Question

I have to write an access query to find a user that doesnt have a particular color. So in this data set I need to find users that don't have the color Magenta.

James - Red
James - Blue
James - Magenta
Gary - Red
Gary - Violet
Jeffry - Red
Jeffry - Magenta
George - Blue
George - Green
April - Magenta
April Blue

The result needs to be just the users without Magenta
Gary
George

 

Any help is appreciated.

I have tried Select Distinct
and Group By and neither are giving the result I want.

 

That gives you


James - Red
James - Blue
Gary - Red
Gary - Violet
Jeffry - Red
George - Blue
George - Green
April Blue

.....

I need it to exclude the whole subset if it has Magenta.

 

SELECT Distinct Name
FROM Table1
WHERE (((Name) Like "Magenta"));

This gives me all the names that HAVE Magenta:
James
Jeffry
April

And if I do

SELECT Distinct Name
FROM Table1
WHERE (((Name) NOT Like "Magenta"));

I get all the names because there are other records that don't have Magenta.

 

I guess I didn't explain it correctly, but I was looking for

2.


However, I think I got it figured out using

Select *
From Table1
Where Color Not in(
Select Distinct Name
From Table1
WHere Color Like "Magenta");


It wasn't working earlier because I was using a a NOT IN Statement with a NOT Like and it was blowing up access.

Thanks guys!