By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,067 Members | 717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,067 IT Pros & Developers. It's quick & easy.

How to fix "Query too Complex" error when trying to rank using subquery?

P: 1
Hi all,

I have a query called [Scan Data MAT Comparison Report] and I want to rank each customer grouped on the field [Banner] and then by descending order on [Current MAT].

I have found many solutions where I can use a subquery to do this eg:

SELECT (SELECT COUNT(*) + 1
FROM [Scan Data MAT Comparison Report] AS C
WHERE (C.Banner = CS.Banner AND C.[Current MAT] > CS.[Current MAT])) AS RankInBanner, CS.[Cust Id], CS.Banner, CS.[Current MAT]
FROM [Scan Data MAT Comparison Report] AS CS
ORDER BY CS.Banner, CS.[Current MAT] DESC;


but every time I do this I get the "Query Too Complex" error. I have tried adding and removing [] to fields and tables with no joy.
Does anyone know what SQL I should use to eliminate this error?

Thanks,
janie
Feb 8 '11 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
Hi Janie. The 'query too complex' message occurs when the Access query engine finds that the tasks involved in processing the current and underlying queries exceed its capabilities.

It is not really the current SQL which is causing it the problem, so it can't be fixed by adding bracketing. It is the work involved in using the existing report query, [Scan Data MAT Comparison Report], as the source of both the main query and the subquery in the current SQL.

I have found that there is only one way to resolve such issues, which is to simplfy the underlying queries by either breaking them down into simpler initial queries joined later to provide the same functionality, or to create temporary tables and use the temporary table as the source for, say, a subquery.

In this case I would suggest that you create a separate named query for the inner subquery in your SQL, one whose sole task is to count the rows meeting the stated conditions. After you store the new query you can then try substituting the named query into your query/subquery combination in place of the inner reference to your reporting query.

If this fails you may need to adopt either the break it down and simplify approaches, or the temporary table approach, to make it work for you.

-Stewart

ps as the following MS support article makes clear, the issue relates to the number of joins in the underlying queries:
http://support.microsoft.com/kb/918814
Feb 8 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.