Limit on Sub-Queries?

[needadvice]

I'm developing a "Master Query" to run a summary report. It requires 33 sub queries. After adding 30 sub-queries to the "Master Query" I get a message that says "query is too complex" and it won't run. Does Access have a limit of 30 and if so how to get around this.
Any help greatly appreciated.

 

[dhookom]

I would find that rather complex! Since we have no idea why you need so many subqueries, I can only suggest you may need to create some temporary tables to use in place of some of the queries.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

needadvice

Hmmmm.
A too complex query usually means that Access can not resolve the query. For example, if you have a one to many (1:M) relationship to one table, and another 1:M to another table, and another 1:M to a third table all with inner joins where not all links have data. Break down your query to find out where it falls apart.

Per this link, Access 2000 supports up to 50 nested subqueries and 255 columns in a SELECT statement, and about 64,000 characters.

http://www.databasejournal.com/features/mssql/article.php/2204341

...Moving on.
Is it possible that your design could be better normalized to reduce the number of nested subquries??

Richard