Getting around the 'Query is too complex' issue

[wisey01]

Hey All,

Recently ran into the 'Query is too complex' error while entering data into my database when attempting to run the following query:

TRANSFORM Count([Summary of Tests].Status) AS CountOfStatus
SELECT [Application List].[TC or #], [Application List].[Test Object], IIf([Test Status Summary]![Number of Incomplete]>0,"Incomplete",IIf([Test Status Summary]![Number of Fails]>0,"Failed","Passed")) AS Status, ConcatRelated("[Defect Number]","[Summary of Tests]","[Summary of Tests].[Test Object] = """ & [Application List].[Test Object] & """") AS [Defect Number], ConcatRelated("[Comments]","[Summary of Tests]","[Summary of Tests].[Test Object] = """ & [Application List].[Test Object] & """") AS Comments, ConcatRelated("[Tester 1]","[Summary of Tests]","[Summary of Tests].[Test Object] = """ & [Application List].[Test Object] & """") AS [Tester 1], ConcatRelated("[Tester 2]","[Summary of Tests]","[Summary of Tests].[Test Object] = """ & [Application List].[Test Object] & """") AS [Tester 2], ConcatRelated("[Tester 3]","[Summary of Tests]","[Summary of Tests].[Test Object] = """ & [Application List].[Test Object] & """") AS [Tester 3]
FROM ([Summary of Tests] RIGHT JOIN [Application List] ON [Summary of Tests].[Test Object] = [Application List].[Test Object]) LEFT JOIN [Test Status Summary] ON [Application List].[Test Object] = [Test Status Summary].[Test Object]
GROUP BY [Application List].[TC or #], [Application List].[Test Object], IIf([Test Status Summary]![Number of Incomplete]>0,"Incomplete",IIf([Test Status Summary]![Number of Fails]>0,"Failed","Passed"))
PIVOT [Summary of Tests].Status;

It seems to have functioned fine until the underlying queries hit a critical mass, and functions fine if I export the data from the underlying query to Excel and then import back into Access.

Is there a way around this issue?

Thanks in advance,

Greg

 

[wisey01]

After some fiddling I have come to the conclusion that if I could get an earlier query to output as a table it would reduce the complexity of the query above, solving the issue.

However the earlier query is a Union query and I can't seem to easily combine the two.

Does any know what the basic SQL would look like?