exclusion query??

[Jerome]

Hi,

(Using MS Access 2003) I've got the following situation:

Let's say I have one table containing individual persons.
And a second table containing their wishlist of things and the their
statuses.

Meaning you could have something like this:

Joe - shoes - unknown
Joe - shirt - granted
Joe - boots - unknown
Sam - car - unknown
Sam - tie - granted
Max - bag - unknown
Max - chair - unknown

And now I want my query to display only those persons who haven't be
granted anything! Which in this case would get me 'Max'.

But how do I create such a query and is it possible at all?? Just
restricting it to 'unknown' is not enough since it would result in Joe,
Sam and Max ...

Thanks a lot!

Jerome

 

[Nikos Yannacopoulos]

Jerome,

Try something like:

SELECT DISTINCT PersonName FROM tblWishList
WHERE PersonName Not In
(SELECT DISTINCT PersonName FROM tblWishList WHERE Status = "granted")

I hope my table and field name assumptions ar eobvious, so you can
change to the actual ones!

HTH,
Nikos