Query is too Complex?

  • Thread starter Thread starter briank
  • Start date Start date
B

briank

When I run my query Access give me a popup menu that
says: "Query is too Complex". Anyway suggestions to work
around this?
 
Can't help without seeing the SQL from the query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
When I run my query Access give me a popup menu that
says: "Query is too Complex". Anyway suggestions to work
around this?
Click to expand...

Simplfy the query <g>...

Seriously, the QTC error arises when the *compiled* query exceeds
64KBytes. A lot goes into the size of a query, some of which you can
modify and some you cannot. Some suggestions:

- Remove any fields that you don't actually need
- Use short aliases for any fields with long names: e.g. instead of

SELECT ... [Annual Payment Expected From Client], [Payment Actually
Received From Client]
....
WHERE [Annual Payment Expected From Client] < [Payment Actually
Received From Client]

use

SELECT ... [Annual Payment Expected From Client] AS ExpPay, [Payment
Actually Received From Client] AS Recd
....
WHERE ExpPay < Recd

- Eliminate or simplify complex expressions such as IIF(), perhaps by
writing a custom VBA function to do the same thing

- If you're using nested subqueries, save the subquery separately and
reference it by query name rather than writing out the full SQL

- If need be, use temp tables to store intermediate data
 
Back
Top