[AccessD] Need help with a query

[Rocky Smolin]

Probably not but the schema's pretty simple here - the both FKs in the
connecting table are indexed.  I think I'll go the code route.  Already
spent an hour trying to construct a query or queries to do this - which I
really can't charge the client for.  Code probably take half as long.

R


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

If you can specify a limit to the number of mailing ID's that can be
associated with any person, then you could, perhaps go for the
non-normalised view And/or if the number of possible lists is not
excessively large, maybe assign the lists numeric references and associate
them with positional markers  in a text string  associated with the person,
so each person has a marker string that can include all the ID's

Remembering that a lot of the BD theory is based on constrained storage
resources of a few years ago, and far slower I/O into much smaller memory
allocations for searching with slower CPU's - So do you really need to have
the data normalised to reduce space usage 

JimB

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

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

--
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