Re: "Query Too Complex" Errors

Answers in-line, following your questions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jake_G" <u30146@uwe> wrote in message
news:6a9ed3f1b7d83@xxxxxx

In my office, we have a complex Access file which we use to create our
monthly reports that we send to our clients. The file was initially
created
two years ago, but since then the company has grown immensely and the file
was updated many times to meet our growing needs. Many of the calculation
that the files does nowadays were not part of the initial design and so
they
are done in a very inefficient way. As a result of that, the file contains
a
few dozens of queries, in the middle of which there's a long chain of
queries,
each based on another query's (or queries') output.

As you can probably guess, we've been having a lot of those "Query Too
Complex" errors. We overcame this problem by adding a few intermediate
tables,
which contain the output of some of the complex queries, and then basing
the
subsequent queries on those intermediate tables, instead of the queries
themselves.

Because this solution is a temporary one, and in light of the fact that
our
company is still growing and we may need to add more functions to this
Access
file, my boss decided to build this file from scratch, only this time
trying
to make it as efficient as possible and take into consideration the needs
that may arise in the future. This project felt into the hands of yours
truly.


Now, I'm sure I can greatly improve this file's efficiency, but I don't
think
there's a way to altogether avoid this chain of complex, subsequent
queries.
This means that I, most likely, am going to encounter the "Query Too
Complex"
error, times and again.

My questions are as following:

1. What are the general guidelines in avoiding the dreaded "Query Too
Complex" error?

The message just means Access doesn't understand the query. Even a simple
query can fail with this message for many reasons. Examples:

a) The bracketing is invalid, or inadequate (ambiguous.)

b) Square brackets are needed around a name containing a space.

c) You confused Access by using a reserved word as a the name of a
table/field/alias.

d) Name AutoCorrect has Access confused about the name of something. More
info on the raft of problems this causes:
http://allenbrowne.com/bug-03.html

e) Access is confused about the data type of a calculated field, so the
calculation needs typecasting. More info:
http://allenbrowne.com/ser-45.html

f) There's an undeclared parameter that has Access confused. Declare it so
JET knows the name and its data type.

g) Delimiters are needed (or should be omitted) around a literal value.

Oh, and of course, it could also be that the query is too complex to do as a
single operation. :-)

2. Should I strive to write one huge query or should I just break it down
to a large number of very simple queries instead?

Your goal is the simplest solution that is totally reliable, as easy as
practical to maintain, reasonably efficient to execute, and interfaced so
any user is clear what's going on. These 4 requirements are often in
conflict.

In practice, I usually start out on a task assuming I will be able to build
the SQL string in code. That gives you great flexibility. For example, if
there are several optional parameters, some of the complexity in the SQL
statement is just handling each case where the user might leave the text box
blank. If you build the SQL string dynamically, you can entirely omit from
the WHERE clause those fields that are not used this time. Similarly, if the
criteria includes a date period (month/quarter/...), you can calculate start
and end dates in VBA, and then patch the literal dates into the SQL string
instead of the entire calculation. This also avoids all problems with data
typing and parameters.

In building that SQL string, you can include several joins and a few
subqueries. If there are 3 or 4 subqueries, I'm starting to worry because
it's fairly easy to crash JET with subqueries. Or there may be obvious
places where pre-processing would be more efficient. If so, it's time to use
some pre-processing, i.e. lower level queries that are called by the main
SQL statement you are working on. These stacked queries may be static, or
you might need to give them criteria specific to this monster you are
writing. You can do that by generating a string for their SQL statement, and
assigning it to the SQL property of the QueryDef, e.g.:
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql
I find that JET usually (but not always) executes stacked queries more
efficiently than subqueries.

If the dynamic SQL statement with its subqueries and stacked queries is
still becoming unweildy or inefficient, I start thinking about using
temporary tables. It's usually just a matter of storing primary key values
so you can limit your SQL statement with an INNER JOIN on the temp table. If
I use a temp table, I always set it up at design time and INSERT/DELETE
(i.e. never a Make Table query.) It usually goes into the front end, so
multiple users don't interfere with each other if they are both running the
same process, and I use a different table prefix name for these tables.

As you gain experience, there will be times when the need for pre-processing
is obvious, so you will start to build your query that way.

3. What's the best way to arrange the initial tables?

Always normalized.
Use a naming convention.
Don't use spaces or other characters that require you to add the square
brackets around names.
Make sure Name AutoCorrect is off.

4. Should I avoid having too many columns in a query?

The limits on your query are:
- 255 fields;
- 4k characters in all fields (not counting BLOBs such as memos, hyperlinks,
OLE Objects, attachments.)

5. Does the number of lines in an input table/query has an effect on the
probability of getting the "Query Too Complex" error?

Not directly.

6. Would a significantly more powerful PC help avoid the error?

No. The problem is with the interpretation of the query statement, not the
processing power needed to execute it.


.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... mark, FTS needs to be very carefully tuned to achieve second response times, ... see SQL Server 2000 BOL title "Full-text Search Recommendations" for more ... When you include the "TOP 100" in your query, you are in fact limiting the ... valid for SQL queries, they often do not apply to FTS queries because the FT ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • RE: Invalid SQL Statement
    ... assumed I would be able to run such a query. ... Is there a function similar to RunSQL that works for select queries? ... >> DoCmd.RunSQL sSQL, False ... >> valid SQL statement. ...
    (microsoft.public.access.formscoding)