query is too complex

[luciegiles]

Hi,

I am using a series of queries containing IIF statements and calculated fields to perform a calcualtion between two dates with a further time period subtracted. It works except I wanted to sort (ascending) the resulting caluclated field (RefToTreat in the last query); I got a 'query is too complex' error. Any ideas how I can avoid/work around this?

The SQL of the queries is as follows:

qryEffectiveStartDate_Assessment - determines the latest of six dates in tblContactDetails and tblAssessmentNonAttendance.

SELECT tblContactDetails.ContactNumber, tblContactDetails.HospitalNumber, tblContactDetails.[ReferralDate/1stContact], IIf([Non-AttendanceCategory1]=2,[Non-AttendanceDate1],IIf([Non-AttendanceCategory1]=3,[Non-AttendanceDate1],[ReferralDate/1stContact])) AS StartDate1, IIf([Non-AttendanceCategory2]=2,[Non-AttendanceDate2],IIf([Non-AttendanceCategory2]=3,[Non-AttendanceDate2],[ReferralDate/1stContact])) AS StartDate2, IIf([Non-AttendanceCategory3]=2,[Non-AttendanceDate3],IIf([Non-AttendanceCategory3]=3,[Non-AttendanceDate3],[ReferralDate/1stContact])) AS StartDate3, IIf([Non-AttendanceCategory4]=2,[Non-AttendanceDate4],IIf([Non-AttendanceCategory4]=3,[Non-AttendanceDate4],[ReferralDate/1stContact])) AS StartDate4, IIf([Non-AttendanceCategory5]=2,[Non-AttendanceDate5],IIf([Non-AttendanceCategory5]=3,[Non-AttendanceDate5],[ReferralDate/1stContact])) AS StartDate5, IIf([StartDate5]>[StartDate4],[StartDate5],IIf([StartDate4]>[StartDate3],[StartDate4],IIf([StartDate3]>[StartDate2],[StartDate3],IIf([StartDate2]>[StartDate1],[StartDate2],[StartDate1])))) AS EffectiveStartDate_Assessment
FROM tblContactDetails LEFT JOIN tblAssessmentNonAttendance ON tblContactDetails.ContactNumber = tblAssessmentNonAttendance.ContactNumber
WHERE (((tblContactDetails.[ReferralDate/1stContact])>#3/31/2011#));
 

qryEffectiveStartDate_FirstTreatment - determines the latest of six dates in tblContactDetails and tblTreatmentNonAttendance.

SELECT tblContactDetails.HospitalNumber, tblContactDetails.ContactNumber, tblContactDetails.[ReferralDate/1stContact], IIf([TreatmentNon-AttendanceCategory1]=2,[TreatmentNon-AttendanceDate1],IIf([TreatmentNon-AttendanceCategory1]=3,[TreatmentNon-AttendanceDate1],[ReferralDate/1stContact])) AS StartDate1_FirstTreat, IIf([TreatmentNon-AttendanceCategory2]=2,[TreatmentNon-AttendanceDate2],IIf([TreatmentNon-AttendanceCategory2]=3,[TreatmentNon-AttendanceDate2],[ReferralDate/1stContact])) AS StartDate2_FirstTreat, IIf([TreatmentNon-AttendanceCategory3]=2,[TreatmentNon-AttendanceDate3],IIf([TreatmentNon-AttendanceCategory3]=3,[TreatmentNon-AttendanceDate3],[ReferralDate/1stContact])) AS StartDate3_FirstTreat, IIf([TreatmentNon-AttendanceCategory4]=2,[TreatmentNon-AttendanceDate4],IIf([TreatmentNon-AttendanceCategory4]=3,[TreatmentNon-AttendanceDate4],[ReferralDate/1stContact])) AS StartDate4_FirstTreat, IIf([TreatmentNon-AttendanceCategory17]=2,[TreatmentNon-AttendanceDate17],IIf([TreatmentNon-AttendanceCategory17]=3,[TreatmentNon-AttendanceDate17],[ReferralDate/1stContact])) AS StartDate5_FirstTreat, IIf([StartDate5_FirstTreat]>[StartDate4_FirstTreat],[StartDate5_FirstTreat],IIf([StartDate4_FirstTreat]>[StartDate3_FirstTreat],[StartDate4_FirstTreat],IIf([StartDate3_FirstTreat]>[StartDate2_FirstTreat],[StartDate3_FirstTreat],IIf([StartDate2_FirstTreat]>[StartDate1_FirstTreat],[StartDate2_FirstTreat],[StartDate1_FirstTreat])))) AS EffectiveStartDate_FirstTreat
FROM tblContactDetails LEFT JOIN tblTreatmentNonAttendance ON tblContactDetails.ContactNumber = tblTreatmentNonAttendance.ContactNumber
WHERE (((tblContactDetails.[ReferralDate/1stContact])>#3/31/2011#));

qryEffectiveStartDate - determines the latest of the two dates returned by the two previous queries. This is [EffectiveStartDate], the start point in the last query (below).

SELECT tblContactDetails.HospitalNumber, tblContactDetails.ContactNumber, tblContactDetails.[ReferralDate/1stContact], qryEffectiveStartDate_Assessment.EffectiveStartDate_Assessment, qryEffectiveStartDate_FirstTreatment.EffectiveStartDate_FirstTreat, IIf([EffectiveStartDate_FirstTreat]>[EffectiveStartDate_Assessment],[EffectiveStartDate_FirstTreat],[EffectiveStartDate_Assessment]) AS EffectiveStartDate
FROM (qryEffectiveStartDate_Assessment RIGHT JOIN tblContactDetails ON qryEffectiveStartDate_Assessment.ContactNumber = tblContactDetails.ContactNumber) LEFT JOIN qryEffectiveStartDate_FirstTreatment ON tblContactDetails.ContactNumber = qryEffectiveStartDate_FirstTreatment.ContactNumber
WHERE (((tblContactDetails.[ReferralDate/1stContact])>#3/31/2011#));

qryPeriodsOfUnavailability_FirstTreat - calculates the periods a person is not available and totals these ([TotalUnavailability]) to be subtracted in the last query below.

SELECT tblContactDetails.HospitalNumber, tblContactDetails.ContactNumber, tblContactDetails.[ReferralDate/1stContact], DateDiff('d',[UnavailabilityStartDate1],[UnavailabilityEndDate1]) AS Period1, DateDiff('d',[UnavailabilityStartDate2],[UnavailabilityEndDate2]) AS Period2, DateDiff('d',[UnavailabilityStartDate3],[UnavailabilityEndDate3]) AS Period3, DateDiff('d',[UnavailabilityStartDate4],[UnavailabilityEndDate4]) AS Period4, DateDiff('d',[UnavailabilityStartDate5],[UnavailabilityEndDate5]) AS Period5, IIf([UnavailabilityStartDate1]>=[EffectiveStartDate],[Period1],0) AS ExcludePeriod1a, IIf([UnavailabilityStartDate1]<=[DateTreatmentStarted1],[Period1],0) AS ExcludePeriod1b, IIf([ExcludePeriod1a]>[ExcludePeriod1b],[ExcludePeriod1a],[ExcludePeriod1b]) AS ExcludePeriod1, IIf([UnavailabilityStartDate2]>=[EffectiveStartDate],[Period2],0) AS ExcludePeriod2a, IIf([UnavailabilityStartDate2]<=[DateTreatmentStarted1],[Period2],0) AS ExcludePeriod2b, IIf([ExcludePeriod2a]>[ExcludePeriod2b],[ExcludePeriod2a],[ExcludePeriod2b]) AS ExcludePeriod2, IIf([UnavailabilityStartDate3]>=[EffectiveStartDate],[Period3],0) AS ExcludePeriod3a, IIf([UnavailabilityStartDate3]<=[DateTreatmentStarted1],[Period3],0) AS ExcludePeriod3b, IIf([ExcludePeriod3a]>[ExcludePeriod3b],[ExcludePeriod3a],[ExcludePeriod3b]) AS ExcludePeriod3, IIf([UnavailabilityStartDate4]>=[EffectiveStartDate],[Period4],0) AS ExcludePeriod4a, IIf([UnavailabilityStartDate4]<=[DateTreatmentStarted1],[Period4],0) AS ExcludePeriod4b, IIf([ExcludePeriod4a]>[ExcludePeriod4b],[ExcludePeriod4a],[ExcludePeriod4b]) AS ExcludePeriod4, IIf([UnavailabilityStartDate5]>=[EffectiveStartDate],[Period5],0) AS ExcludePeriod5a, IIf([UnavailabilityStartDate5]<=[DateTreatmentStarted1],[Period5],0) AS ExcludePeriod5b, IIf([ExcludePeriod5a]>[ExcludePeriod5b],[ExcludePeriod5a],[ExcludePeriod5b]) AS ExcludePeriod5, [ExcludePeriod1]+[ExcludePeriod2]+[ExcludePeriod3]+[ExcludePeriod4]+[ExcludePeriod5] AS TotalUnavailability
FROM (qryEffectiveStartDate RIGHT JOIN tblContactDetails ON qryEffectiveStartDate.ContactNumber = tblContactDetails.ContactNumber) LEFT JOIN tblUnavailability ON tblContactDetails.ContactNumber = tblUnavailability.ContactNumber
WHERE (((tblContactDetails.[ReferralDate/1stContact])>#3/31/2011#));

qryReferraltoFirstTreatment - calculates the time between [EffectiveStartDate] and [DateTreatmentStarted1] minus [TotalUnavailability] - this is field I wish to sort.

SELECT tblPatientDetails.PatientForename, tblPatientDetails.PatientSurname, tblPatientDetails.HospitalNumber, tblPatientDetails.HospitalNumber2, tblContactDetails.[ReferralDate/1stContact], qryEffectiveStartDate.EffectiveStartDate, tblContactDetails.DateTreatmentStarted1, qryPeriodsOfUnavailability_FirstTreat.TotalUnavailability, ([DateTreatmentStarted1]-[EffectiveStartDate])-[TotalUnavailability] AS RefToTreat
FROM tblPatientDetails INNER JOIN ((qryEffectiveStartDate RIGHT JOIN tblContactDetails ON qryEffectiveStartDate.ContactNumber = tblContactDetails.ContactNumber) LEFT JOIN qryPeriodsOfUnavailability_FirstTreat ON tblContactDetails.ContactNumber = qryPeriodsOfUnavailability_FirstTreat.ContactNumber) ON tblPatientDetails.HospitalNumber = tblContactDetails.HospitalNumber
WHERE (((tblContactDetails.[ReferralDate/1stContact])>#3/31/2011#));

thanks
L

[beacon]

Hi L,

I'm going to throw this out there for you to check on first (before I look at the queries in depth). Are all of the table fields that you are working with as dates set as date/time fields on the underlying table?

I seem to remember running into a similar error a long time ago, and that I was able to fix it by converting one of the fields from text to date (using the CDate() function) in the query (instead of changing the data type from text to date/time in the underlying table).

Try this on for size and see if that's the issue.

Hope this helps,
beacon

[luciegiles]

Hi Beacon,

I wish it were that simple, but alas all the date fields from tables are set as date/time fields (short date).

L

[beacon]

This is just a shot in the dark because nothing is jumping out at me on your SQL, but are your queries setup so that you could perform the WHERE clause on just the last query in the group? I'm curious if maybe that is having an effect on how the data is processing.

I performed a search on your error message and returned the following link from the Microsoft Knowledge Base: http://support.microsoft.com/kb/103429

Two things that jump out from the article that you may want to fix:

1. Move complex expressions to a user-defined function that does all the evaluating
2. Reduce expressions in underlying queries

[NeoPa]

Generally I would tidy up your SQL. There's many times an appropriate amount of SQL to ask someone to go through for you. What I did see was that there is scope for using the Choose() function in place of some of the IIf()s, but I'm sure there is scope for more. Unless you're doing rocket science I can't imagine you need anything as complex (clumsy) as you have there. I may be wrong, but I've never seen anything as complex as that that eventually proved necessary.

The real lesson here is not ever to let your SQL (or any other code for that matter) get into such a state. If you're tidy when you build it up, it need never get to this state.

By the way, it is more likely that people will look at your code for you if you format it for display on the page so that it's readable. SQL doesn't care where it finds its commands except that it recognizes white space. Here's an example to show that even very large and clumsy SQL can be legible at least :

SELECT tPD.PatientForename
     , tPD.PatientSurname
     , tPD.HospitalNumber
     , tPD.HospitalNumber2
     , tCD.[ReferralDate/1stContact]
     , qESD.EffectiveStartDate
     , tCD.DateTreatmentStarted1
     , qPUF.TotalUnavailability
     , [DateTreatmentStarted1]-
       [EffectiveStartDate]-
       [TotalUnavailability] AS [RefToTreat]
 
FROM   [tblPatientDetails] AS tPD
       INNER JOIN
     (([qryEffectiveStartDate] AS qESD
       RIGHT JOIN
       [tCD.] AS tCD
  ON   qESD.ContactNumber = tCD.ContactNumber)
       LEFT JOIN
       [qryPeriodsOfUnavailability_FirstTreat] AS qPUF
  ON   tCD.ContactNumber = qPUF.ContactNumber)
  ON   tPD.HospitalNumber = tCD.HospitalNumber
 
WHERE  (tCD.[ReferralDate/1stContact]>#3/31/2011#)

[Stewart Ross]

To add to what my very experienced colleague NeoPa has said, when SQL expressions start to look complex or untidy it points to problems in either the way the data is defined or the way the SQL is being used to process that data. If it looks complex it is often an indication that something is missing elsewhere in the approach to the problem - something I think relating in your case to defining how patient appointment misses are calculated and recorded.

In the examples you provide, the nested IIFs are quite difficult to follow, and I would echo what Beacon has mentioned (from the MS knowledgebase article) about replacing these with custom functions to do the processing for you. The query optimisers in the Access database engine cannot optimise IIF statements, and I would bet that the 'query too complex' error results from the problems the query engine faces in dealing with six levels of SQL statements, five of which have LEFT or RIGHT joins, each of which contains multiple (sometimes nested) IIFs.

I do not know enough about your application to advise how to optimise it, but it does feel as if the rule set you are applying through your IIFs indicates either that there may be more tables needed than you currently have (if some of the work can be done by joining to a lookup table based on current categories), or that custom functions may be needed (to implement specific rules that can't be encoded in a lookup table, such as which date to apply given the number of missed appointments etc).

-Stewart

[luciegiles]

Hi all,

Thanks for the responses. For ease and speed I have decided to make the final query a make table one and do further querying from there - clumsy I'm sure but it works for now.

NeoPa - I wrote the queries in design view and simply copied from the SQL window to paste into my question. I'm not very familiar with using SQL but will endeavour to simplify this code and replace the above solution with a neater one. Thanks for the tip re format also.

Beacon, Stewart - again I'm not familiar with user-defined functions but have started to do a little research. It seems these are only available in ADP not databases - am I missing something?

thanks again
L

[Stewart Ross]

I'm puzzled about the source of your info re ADP and functions, Lucie, as you can define functions within a code module in any form of Access database, just as you can in Excel and the other Office applications.

-Stewart

[NeoPa]

If, while designing an Access object (like a report, or even one of the many sections or controls on one), you look at the properties window, you will find a number of Event properties (On Click; After Update; On Enter; etc). Double-clicking the names of any of these properties will set the value to "[Event Procedure]". If you then click on the ellipsis button immediately to the right a new window will open allowing access to the code of your project. It will also, in this case, have created the stub of an Event Procedure for you. In here you can create and modify (and even monitor the execution of) your project's code (See Debugging in VBA for more on this).

I would say most MDB files, certainly of those done at a professional level, will have some code associated. If you need help with any of this please feel free to post your specific question (or questions) in the forum.