[AccessD] Need help with a query

Bill Benson bensonforums at gmail.com
Thu Apr 2 08:14:14 CDT 2015


So the 1024 limit is by "design" hehe. But the increased the limit for
those who SQueaLed.
On Apr 2, 2015 8:52 AM, "Heenan, Lambert" <Lambert.Heenan at aig.com> wrote:

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