Query too complex

[JPM]

Access 2.0

I've encounted a situation where the error message "Query too complex"
occurs. There are 13 tables joined together and this is a "Make Table"
query. There are no expressions or user defined functions called within
the query. I've also encountered this problem, though less often, in Access
2000.

Question #1: Why might this be happening? If the joins were improper I
know I'd be getting a different problem.

Question#2: I encountered the following statment on MS' KB site:
Stacked queries 've solved the problem scenario above many times. Why is it
a problem?

a.. Avoid stacked query objects, such as situations in which Query1 is used
to pull data from Table2 and Query2 filters the data. Pulling information
together in one query is preferable to having multiple queries, each doing
portions of the task.

Thanks for the input,

JPM

 

[John Vinson]

Access 2.0

Eeep! Do you still drive a 1952 model car? <g>

Seriously - 2.0 has not been supported for years, and uses 16-bit
technology. If it's still working for you great, but you're on
increasingly thin ice (already it's impossible to upgrade directly to
2000, much less to 2002 or 2003); you'll need to get a copy of
Access97 and use it to relay.

I've encounted a situation where the error message "Query too complex"
occurs. There are 13 tables joined together and this is a "Make Table"
query. There are no expressions or user defined functions called within
the query. I've also encountered this problem, though less often, in Access
2000.

Question #1: Why might this be happening? If the joins were improper I
know I'd be getting a different problem.

The QTC error arises when the *compiled* query exceeds a certain size.
I don't recall what that size was in 2.0 but I believe it's 64KBytes
in later versions.

Why a MakeTable at all? IME they're very rarely needed.

Question#2: I encountered the following statment on MS' KB site:
Stacked queries 've solved the problem scenario above many times. Why is it
a problem?

a.. Avoid stacked query objects, such as situations in which Query1 is used
to pull data from Table2 and Query2 filters the data. Pulling information
together in one query is preferable to having multiple queries, each doing
portions of the task.

A stacked query has additional system overhead, increasing the size of
the compiled query over and above that of the same operations built
into one large query. You may hit QTC either way but you'll hit it
sooner with stacked queries.

Sometimes, if it's really necessary to create this new table, you'll
need to do it in stages - say a maketable query followed by an append
or update query to bring in the data that you couldn't get to work
with the first query.