[AccessD] Need help with a query

[Heenan, Lambert]

Just checked with a very simple query and the limit I hit was a 1024 character limit for the Query By Design grid. The query runs, but can only be viewed as SQL in design mode.


Lambert


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, April 02, 2015 8:45 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

Interesting. I could swear I hit the limit recently with a large, comma delimited In() clause which would not run in JET but was OK as pass-through query.

Lambert

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

Surprised by a limit of 2k characters, I searched and found the following relevant to Access 2010:

https://support.office.com/en-za/article/Access-2010-specifications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854

"Number of characters in an SQL statement  Approximately 64,000*
 
*Maximum values might be lower if the query includes multivalued lookup fields."

Earlier versions may have had lower limits, but let's hope that 2010 and beyond are at least as much as this.

This particular spec relates to Query objects.  Does anybody know if a similar limit exists for SQL that's built and executed in code?  Just wondering if there may be a difference.


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

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