[AccessD] Need help with a query

[Janet Erbach]

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
>