469,300 Members | 2,326 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

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

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
1 8711
Stewart Ross
2,545 Expert Mod 2GB
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.

Similar topics

33 posts views Thread by Steven Bethard | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.