[AccessD] Need help with a query

[Rocky Smolin]

I've also used queries in queries.  Very useful technique. And I've tried a
couple of approaches that way as well in this case.  Tried the unmatched
query wiz.  But when a thing gets too snaky with queries I just write a bit
of code to do it. 

So either it's a brain fart or I'm going the code route.  Code route is real
easy but as there will be a couple thousand persons in the person table I
don't want to have the delay when the user selects the list to be displayed
or selects one of the options in the option frame 1) all 2) only list
member, 3) only Not Members.

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Janet Erbach
Sent: Wednesday, April 01, 2015 9:33 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need help with a query

Rocky -

Can all that really be done in one query?  I have always broken down
something like that into 2 steps...

Janet

On Wed, Apr 1, 2015 at 11:07 AM, Rocky Smolin <rockysmolin at bchacc.com>
wrote:

> 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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com