I have a fairly simple query, below, as a recordsource for a form, but it produces a very annoying Query Too Complex message only when I add the final ORDER BY clause to the SQL Statement. Everything works fine until I add the ORDER BY clause.
I have seen some of the solutions with a temp table, but this db is used by more than one user simulataneously and a portion of the WHERE clause is generated dynamically from radio buttons on the form, so each user will have a different recordsource. Note: if I disable this dynamic portion, it works fine even with the ORDER BY clause... it's like there are just one or two too many conditions ...
I also tried generating a temporary recordset in memory and using it to set the form's recordset, but I'm missing something, it won't let me do that.
See it's not a very big query...
SELECT * FROM
(SELECT P.PersID, P.Rank, P.LastName, P.Initials, P.Sex, P.Volunteer, Left(P.Sex,1) AS SexAbvn, P.MOCs,
Left([Rank],InStr([Rank],"/")-1)) AS RealRank,
(SELECT TOP 1 D.DeploymentID FROM DeploymentTable D WHERE D.PersID = P.PersID AND D.DeploymentDate < Date() AND D.RedeploymentDate >= DateAdd("m",2,D.DeploymentDate) ORDER BY D.RedeploymentDate DESC) AS LastDeploymentID,
(SELECT TOP 1 D.RedeploymentDate FROM DeploymentTable D WHERE D.PersID = P.PersID AND D.DeploymentDate < Date() AND D.RedeploymentDate >= DateAdd("m",2,D.DeploymentDate) ORDER BY D.RedeploymentDate DESC) AS SortKey,
(SELECT TOP 1 S.ScreeningID FROM ScreeningTable S WHERE S.PersID = P.PersID ORDER BY ScreeningIssued DESC) AS LastScreeningID,
(SELECT QualificationName From QualificationTable WHERE PersID = P.PersID AND QualificationName="QL 5 (Journeyman)") AS QL5,
SELECT QualificationName From QualificationTable WHERE PersID = P.PersID AND QualificationName="OSA") AS OSA,
(SELECT Top 1 QualificationName From QualificationTable WHERE (PersID = P.PersID AND (QualificationName="Sig Dev" OR (QualificationName="QL 5 (Journeyman)" AND QualificationDate>#8/1/03#))) ORDER BY QualificationName DESC) AS SigDev
FROM PersInfoTable_All AS P
WHERE (P.SectionList <> "TSB IS CELL" Or P.SectionList Is Null)
AND (P.MOCs="291" Or P.MOCs="Other")
WHERE Rank="Cpl/LS" AND QL5 IS NOT NULL AND OSA IS NOT NULL <-- this portion is inserted depending on the radio button values on the form
ORDER BY Volunteer, SortKey;
Thanks lot! Why do I embed two SELECT Statements? Well the SortKey, QL5 and OSA fields are created by the first query, thus filtering or sorting using them does not work unless I layer another query on top.