[AccessD] Need help with a query

Heenan, Lambert Lambert.Heenan at aig.com
Wed Apr 1 12:32:13 CDT 2015


You don't need any joins, just a query on tblMailingListPersons with a sub query on the user ID. Like this

SELECT tblPersons.ID, tblPersons.STREMAIL, tblPersons.STRFIRSTNAME, tblPersons.STRLASTNAME
FROM tblPersons
WHERE (((tblPersons.ID) Not In (select PersonsID from tblMailingListPersons  where tblMailingListPersons.MailListID=1 )));

(made some guesses about field names). So all that's need is to replace the where "tblMailingListPersons.MailListID=1" with a reference to the form where the mail list is chosen.


Lambert


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Wednesday, April 01, 2015 12: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



More information about the AccessD mailing list