Upper limit on number of tables/queries in a query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've constructed a query that relies on data from 9 other queries, using the
same single field from each of the 9. I've tried to expand this query to
include the same data from a 10th query, but now I get an error message
"query too complex" when i run it. Have I run into some sort of upper limit
here? The 9-query version ran fine every time. The 10th query is structured
exactly the same as the other nine, varying only one select criterium--same
criterium varied in other 9 queries.
 
I've constructed a query that relies on data from 9 other queries, using the
same single field from each of the 9. I've tried to expand this query to
include the same data from a 10th query, but now I get an error message
"query too complex" when i run it. Have I run into some sort of upper limit
here? The 9-query version ran fine every time. The 10th query is structured
exactly the same as the other nine, varying only one select criterium--same
criterium varied in other 9 queries.
Click to expand...

The dreaded "Query Too Complex" error arises when the *compiled*
version of your query exceeds 64KBytes (at least in some versions of
Access). I can suggest a couple of possible solutions:

- Use one- or two-letter aliases for fieldnames; i.e. rather than
using a fieldname like

[qryExtractAllWidgets_2004].[WidgetDiameter]

alias this field as WD and just use [WD] in the WHERE, ORDER BY, JOIN
etc. portions of your queries.

- Rethink the design. It sounds like a lot of redundancy - anytime you
have ten identical queries, I have to think that there may be some way
to use *one* query instead, perhaps a Crosstab query.

- If the goal is a printout, consider using a Report with Subreports
(each based on its own simple query).

John W. Vinson[MVP]
 
Back
Top