Solved

Help: Query Too Complex when adding the ORDER BY clause

Posted on 2003-11-11
5
349 Views
Last Modified: 2012-08-13
Hi!
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,
 IIf(P.Environment="Sea",Right([Rank],Len([Rank])-InStr([Rank],"/")),
 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")
  AND (P.Unit="CFSOC")
  AND P.Active=True)
 
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.
0
Comment
Question by:djfred
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Hey djfred!

  I hate to be the one to belabor the obvious but if the message is "Query Too Complex" then is it is not "fairly simple"
  That SQL statement is complex WITHOUT the Order By.
 
  You would do well to perhaps consider a UNION query before doing the main SELECT from the UNION query.

  Let's remember that you are dealing with Access, not SQLServer or Oracle.

regards
Jack
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
This is work in progress.  Try this to make sure we are in correct path.  I will add other tables, fields, and criteria later.

SELECT *, Left(P.Sex,1) AS SexAbvn, P.MOCs, IIf(P.Environment="Sea",Right([Rank],Len([Rank])-InStr([Rank],"/")), Left([Rank],InStr([Rank],"/")-1)) AS RealRank, D.RedeploymentDate FROM PersInfoTable_All As P Inner Join DeploymentTable On P.PersID=D.PersID Where D.DeploymentDate < Date() AND D.RedeploymentDate >= DateAdd("m",2,D.DeploymentDate) ORDER BY D.RedeploymentDate DESC

Mike
0
 

Author Comment

by:djfred
Comment Utility
Jack:
Thanks for the prompt reply...
However, I'm not sure I understand the UNION business in this case... I'm not currently using a UNION and I don't what portion I could be using as a UNION.

I saw another post on a similar topic at: http://www.experts-exchange.com/Databases/MS_Access/Q_20714212.html
and his query was about 6 times the size of mine... and I guess he got it working in the end... life is so unfair ;)

Also, what do you think of the idea of a temporary table in a form of a recordset in the VBA code... I am thinking that if I create a temporary table as a recordset and then use that as the form's record set, then apply the ORDER BY property on the form, it would be ok, because the form would then base itself off this recordset... is that a good idea?  I tried the obvious:
DIM rst AS DAO.Recordset
Set rst = CurrentDB.OpenRecordset(qryString)
Me.Recordset = rst
Me.OrderBy = "Volunteer, SortKey"
Me.OrderByOn = true

but I get an error message back...
what other temporary table solution would I be able to look at in a multi-user interactive form environment?
lastly, I managed to get the results I wanted by using a completely different query construct using LEFT JOINs but it is very slow:
only about 300 records in the main PersInfoTable once all the WHERE are through, and about 1500 records in the DeploymentTable out of which I retrieve everyone's last (so no more than 300) and about the same for ScreeningTable (thus no more than 300)....
The construct seem, honestly, quite a bit more complex but Access seem to be able to digest it better:  is the speed issue with LEFT JOIN common because the engine first compute the cartesian product?

____
prequery:

SELECT P.PersID, P.ServiceNumber, P.Rank, P.LastName, P.Initials, P.Sex, P.Unit, P.MOCs, P.Environment, P.SectionList, P.Volunteer, D.DeploymentDate, D.RedeploymentDate, D.Location
FROM PersInfoTable AS P LEFT JOIN [SELECT * FROM DeploymentTable WHERE RedeploymentDate> DateAdd("m",2,DeploymentDate)]. AS D ON P.PersID=D.PersID
WHERE (((P.Unit)="CFSOC") AND ((P.MOCs)="291" Or (P.MOCs)="OTHER") AND ((P.SectionList)<>"TSB IS CELL" Or (P.SectionList) Is Null) AND ((D.RedeploymentDate)=(SELECT MAX(DD.RedeploymentDate) FROM (SELECT * FROM DeploymentTable WHERE RedeploymentDate> DateAdd("m",2,DeploymentDate)) AS  DD WHERE DD.PersID = P.PersID) Or (D.RedeploymentDate) Is Null));

mainquery also rowsource (interactive filtering done in this case by an 81 line conditional setFilter macro!!! <- another nemesis of mine ):

SELECT P.PersID, P.ServiceNumber, P.Rank, IIf(P.Environment="Sea",Right(P.Rank,Len(P.Rank)-InStr(P.Rank,"/")),Left(P.Rank,InStr(P.Rank,"/")-1)) AS RealRank, P.LastName, P.Initials, Left(P.Sex,1) AS SexAbvn, P.Unit, P.MOCs, P.Environment, P.SectionList, P.Volunteer, P.DeploymentDate AS LastDeployment, P.RedeploymentDate AS LastRedeployment, P.Location, S.ScreeningIssued, S.ScreeningCompleted, S.ScreeningResult, S.RescreeningDate, (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 [DeploymentList Prequery] AS P LEFT JOIN ScreeningTable AS S ON P.PersID=S.PersID
WHERE (((S.ScreeningIssued)=(SELECT MAX(ScreeningIssued) FROM ScreeningTable SS WHERE SS.PersID = P.PersID) Or (S.ScreeningIssued) Is Null))
ORDER BY P.Volunteer, p.RedeploymentDate;




0
 
LVL 32

Accepted Solution

by:
jadedata earned 200 total points
Comment Utility
If you create and manage the temp tables specific to the occurance (put them in a fresh database in a specific LOCAL place with the users name and a datetimestamp ie jadedata200311112300.mdb) you can use it thru it useful life and then destroy it after it's used up, or retain it as an archive mdb.  You can use a template mdb as an empty starter copy and fill it with useful data using a "series of queries" that contain complex expressions into a pre-summarized format.

Once it works, it works forever (or until the next spec change).  But you won't have to completely redo the operation based on the addtion of a few needed summary data elements.

You will notice that the end result of the question you referenced was the acceptance of my proposed solution including the filling of temp tables, with a supporting comment from one of the best Access guys on the planet, 1William. (my hero!)

Complexity has it's place, but computers can be best used when you try to keep it simple.

Now where do we go from here...?





 
0
 

Author Comment

by:djfred
Comment Utility
Jack... I've yet to try the temp table idea, I think I can make it work... but is it worth for me to investigate the recordset option, that is creating a recordset and setting the form's recordset to it, in say a procedure called by the form open event?

Thanks for your help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now