Correct Join Type and Query Statement

[rblair11]

I've been struggling with this for awhile now. I've been trying to research a solution but I'm not even sure what to search for.

I have the following table structure:

tblContacts
Contact_ID (PK)
First_Name
Last_Name

tblBidPack
Bid_Pack_ID (PK)
Bid_Pack_Description

tblDistributionList
List_ID (PK)
Bid_Pack_ID (FK) (1-M)
Contact_ID (FK) (1-M)

What I would like to do is create a query to display all of the contacts from tblContacts that are not associated with a certain Bid_Pack_ID. My end goal is to create a form where I enter Distribution List Information. I want a listbox on the left with all of the contacts from tblContacts and a listbox on the right with contacts that have been selected for a particular Bid_Pack. When the user double clicks an item on the left it should show up on the right and be removed from the left. I'm looking for the same functionality as when you use the design wizard and you choose fields to include/exclude.

For example, let's say I want to add contacts for distribution list "A". My thought is the box on the left would show all the contacts from tblContacts that aren't selected for distribution list "A" and the box on the right would show all of the contacts that are part of distribution list "A". As you double click an entry it would perform the appropriate record creation/deletion and each box would be required.

So back to my original question, what is the correct method (relationship, join, querydef) to select all of the contacts from tblContacts that haven't been assocatied with a particular bid pack?

Thanks for any help you can provide. Even a nudge in the right direction would be appreciated.

 

[FoFa]

Try something like:

SELECT Contact_ID
FROM tblContacts C
WHERE C.Contact_ID NOT IN
(SELECT D.Contact_ID FROM tblDistributionList D
INNER JOIN tblBidPack B On B.Bid_Pack_ID = D.Bid_Pack_ID
WHERE Bid_Pack_ID = {some criteria you supply})

 

[rblair11]

The query works great, thank you. Now I have to work on the form functionality.