[AccessD] Need help with a query

[James Button]

Thinking around your problem:

You need to assemble a list of all persons that are not in a list of persons
with the selected mailing list

It seems to me that you will need to go for the list of persons in the mailing
list with that ID first,
and then from a list of all the persons, exclude all persons that are in that
list.

Maybe select from a list of all persons with marker to indicate they have the
mailing list ID  associated with them, or they don't have that list id
And then extract from that 

Left - inner outer joins - whatever 

JimB
  

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky
Smolin
Sent: Wednesday, April 01, 2015 5:08 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Need help with a query

Dear List:
 
I'm trying to make a query do something I would usually just do with a bit
of code and am having some difficulty.
 
I have 
1) a table tblMailLists (PK, list name), 
2) a table tblPersons (PK & Persons Name) and 
3) a connecting table tblMailingListPersons (MailListID FK and Persons ID
FK. Pretty standard stuff
 
So people can be assigned to one or more mailing lists.
 
I need to assemble into a temp table and display in a form all the people
not already on a specific mailing list so that the user can assign them to
the selected mailing list.
 
So I connected tblPersons with tblMailingListPersons - all records from
tblPersons and only those records in tblMailingListPersons - and used the
criteria:
 
MailListID <> (user's selected list) OR Is Null.  
 
This gives me all the people not assigned to the selected list. But if a
person is assigned to another list, of course they show up on this list as
well (Person A is assigned to lists 1 and 2 - so the criteria filter out
anyone who's on List 1 but passes through someone on List 2).  
 
I can't figure out how to suppress all the people who are on the user's
selected mailing list even if they're assigned to another mailing list.
 
And direction appreciated.  If it's too snaky I'll just write some code to
populate the temp table. 
 
MTIA
 
Rocky
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com