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

[janiecarter]

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

[Stewart Ross]

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