[AccessD] Need help with a query

Bill Benson bensonforums at gmail.com
Wed Apr 1 16:16:24 CDT 2015


This could also work, perhaps if tweaked correctly.

SELECT A.ID, A.STREMAIL, A.STRFIRSTNAME, A.STRLASTNAME
FROM tblPersons as A
WHERE Not Exists (select B.ID from tblMailingListPersons as B
     where B.MailListID=1 and A.ID = B.FKPersonID)

This assumes that tblPersons.ID is represented in tblMailingListPersons as
FKPersonID, and that the field ID is the PK of each respective table.

I have aliased the table names in honor of Lambert's comment about SQL
string length limitations
:-D
On Wed, Apr 1, 2015 at 2:47 PM, Rocky Smolin <rockysmolin at bchacc.com> wrote:

> Not In?   Wow.  Who knew?  (well you, of course). :) After 20 years you'd
> think I'd know pretty much the whole language.
>
> I'll give that a try.  Thanks Lambert
>
> R
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Heenan, Lambert
> Sent: Wednesday, April 01, 2015 10:32 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Need help with a query
>
> 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
>
> --
> 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
>


More information about the AccessD mailing list