Minus Operator

[damdoumaa]

Hello,
I was wondering if I can use the MINUS operator in MS ACCESS 2000!!!

I have a contacts database that also contains multiple mailing lists.

I have the following tables:
Contacts (ContactID, Fname, Lname .......etc)
MailingLists (MailingListNb, MailingListName)
MailingListMembers (MailingListNb, ContactID)

Everytime the user searches for a contact, I want to display the names of the Mailing lists that the contact is on (that's very easy)

The hard part, is that I also want to display inside another combobox or a listbox the names of the mailing lists that the contact is NOT on ONLY. So that the user will be able to add the contact to one or more of these mailing lists.

I want to do something like that:

SELECT MailingLists.MailingListNb
FROM MailingLists
MINUS
(SELECT MailingLists.MailingListNb
FROM MailingLists, MailingListMembers
WHERE ContactID = [Forms]![FrmContact].[ContactID]);

This is giving me an error, I guess that the MINUS operator is not supported in MS ACCESS. Is there any other way for doing this?

Any help will be greatly appreciated!

 

[damdoumaa]

Never Mind!!
I got this solved by using "NOT In" operator instead of MINUS.

Thanks anyway!

 

[Pat Hartman]

Subselects can be slow in Jet tables. You can achieve the same results by creating an "unmatched query". Join the two tables/queries with a left join and then select only rows where the key field from the "right" table is null.