Crosstab Query is too complex

[Paul H]

I am running an "upgraded" version of a database vs. the original version, checking to make sure the results are identical for every report. I have been substituting form values for prompts in these queries. The top level crosstab query has no parameters listed and uses a Union query as it's data source. We have checked each old query against each new query and have explicitly declared parameters in the new queries as the form controls in place of the prompts in the old queries. I've actually done this in about 40 reports so far. This last one has been a headache. Every query runs normally and returns the same data in both the new and the old databases till you get to the crosstab query, the only one I did not edit. The old one runs normally. The new one declares, "Query is too complex."

Here's a side by side comparison of the parameter windows and the relate SQL from both a new query and an old query. In this comparison there is one parameter I have left as a prompt.
The only query that does not work in the new database is the crosstab query which is identical in both the new and the old databases You can see that at the bottom of this post.



It's Five o'clock here so I'll pick up the answers in the morning.



New Query

PARAMETERS [Forms]![frmMainMenu]![cboFacility] Text ( 255 ), [Fiscal Year] Text ( 255 ), [Forms]![frmMainMenu]![cboAdult/Juvenile] Text ( 255 );
SELECT Admission.FacCode AS faccode, fy([admit_date]) AS fisyear, IIf([legal_grouping] Is Not Null,[legal_grouping],"OTHER") AS LGroup, Format([admit_date],"mmm") AS rptmonth, Admission.P_RegNo, Admission.Admit_Date, IIf([faccode]="703" Or [faccode]="705",[FacilityAbbrev] & "-" & [unit],[FacilityAbbrev]) AS facunit, LkLegalGroups.Age_Group
FROM (Admission LEFT JOIN LkLegalGroups ON Admission.AdmLegalStatus = LkLegalGroups.Legal_Status_Code) LEFT JOIN LkFacilityAbbrevs ON Admission.FacCode = LkFacilityAbbrevs.Currfaccode
WHERE (((Admission.FacCode) Like [Forms]![frmMainMenu]![cboFacility]) AND ((fy([admit_date])) Like [Fiscal Year]) AND ((Admission.P_RegNo)<>"999999") AND ((LkLegalGroups.Age_Group) Like [Forms]![frmMainMenu]![cboAdult/Juvenile]))
ORDER BY Admission.FacCode, fy([admit_date]), IIf([legal_grouping] Is Not Null,[legal_grouping],"OTHER"), Admission.Admit_Date;

Old Query

PARAMETERS [Facility Code] Text ( 255 ), FY Text ( 255 ), [Adult or Juvenile] Text ( 255 );
SELECT Admission.FacCode AS faccode, fy([admit_date]) AS fisyear, IIf([legal_grouping] Is Not Null,[legal_grouping],"OTHER") AS LGroup, Format([admit_date],"mmm") AS rptmonth, Admission.P_RegNo, Admission.Admit_Date, IIf([faccode]="703" Or [faccode]="705",[FacilityAbbrev] & "-" & [unit],[FacilityAbbrev]) AS facunit, LkLegalGroups.Age_Group
FROM (Admission LEFT JOIN LkLegalGroups ON Admission.AdmLegalStatus = LkLegalGroups.Legal_Status_Code) LEFT JOIN LkFacilityAbbrevs ON Admission.FacCode = LkFacilityAbbrevs.Currfaccode
WHERE (((Admission.FacCode) Like (IIf(getfaccode()="720",[facility code],getfaccode()))) AND ((fy([admit_date])) Like [FY]) AND ((Admission.P_RegNo)<>"999999") AND ((LkLegalGroups.Age_Group) Like [Adult or Juvenile]))
ORDER BY Admission.FacCode, fy([admit_date]), IIf([legal_grouping] Is Not Null,[legal_grouping],"OTHER"), Admission.Admit_Date;

Crosstab Query

SELECT Admission.FacCode AS faccode, fy([admit_date]) AS fisyear, IIf([legal_grouping] Is Not Null,[legal_grouping],"OTHER") AS LGroup, Format([admit_date],"mmm") AS rptmonth, Admission.P_RegNo, Admission.Admit_Date, IIf([faccode]="703" Or [faccode]="705",[FacilityAbbrev] & "-" & [unit],[FacilityAbbrev]) AS facunit, LkLegalGroups.Age_Group
FROM (Admission LEFT JOIN LkLegalGroups ON Admission.AdmLegalStatus = LkLegalGroups.Legal_Status_Code) LEFT JOIN LkFacilityAbbrevs ON Admission.FacCode = LkFacilityAbbrevs.Currfaccode
WHERE (((Admission.FacCode) Like [Forms]![frmMainMenu]![cboFacility]) AND ((fy([admit_date])) Like [Fiscal Year]) AND ((Admission.P_RegNo)<>"999999") AND ((LkLegalGroups.Age_Group) Like [Forms]![frmMainMenu]![cboAdult/Juvenile]))
ORDER BY Admission.FacCode, fy([admit_date]), IIf([legal_grouping] Is Not Null,[legal_grouping],"OTHER"), Admission.Admit_Date;

[CJ_London]

your cross tab query appears to be missing the parameters statement for the parameters here

WHERE (((Admission.FacCode) Like [Forms]![frmMainMenu]![cboFacility]) AND ((fy([admit_date])) Like [Fiscal Year]) AND ((Admission.P_RegNo)<>"999999") AND ((LkLegalGroups.Age_Group) Like [Forms]![frmMainMenu]![cboAdult/Juvenile]))


Also, why are you using like? Like should only be used with wildcard characters which you are not using

The 'query too complex' error usually occurs when there is a problem with the data - either/or wrong data types, null values, some error in a calculated field (e.g. div by 0)

[Paul H]

Pardon my sloppiness. I did not post the Crosstab SQL. I was in too big of hurry to get home. Here is the actual SQL.

[TRANSFORM Count([Admissions by Facility and FY Inc Revoc].P_RegNo) AS [The Value]
SELECT [Admissions by Facility and FY Inc Revoc].facunit, sortmonth2([admit_date]) AS MonthOrder, [Admissions by Facility and FY Inc Revoc].rptmonth, Count([Admissions by Facility and FY Inc Revoc].P_RegNo) AS Total
FROM ([Admissions by Facility and FY Inc Revoc] INNER JOIN LkCtOrd_Facility ON [Admissions by Facility and FY Inc Revoc].faccode = LkCtOrd_Facility.CSBCode) INNER JOIN LkFacilityAbbrevs ON LkCtOrd_Facility.CSBCode = LkFacilityAbbrevs.Currfaccode
GROUP BY [Admissions by Facility and FY Inc Revoc].facunit, sortmonth2([admit_date]), [Admissions by Facility and FY Inc Revoc].rptmonth
ORDER BY sortmonth2([admit_date])
PIVOT [Admissions by Facility and FY Inc Revoc].LGroup In ("EVALN","NGRI","REVOC","DOC","JAIL","IST","URIST","OTHER");

There were no Parameters in the original crosstab query (which runs) so I did not include them in the new database. This produces the same error with the parameters included.

PARAMETERS [Forms]![frmMainMenu]![txtFrom] DateTime, [Forms]![frmMainMenu]![txtTill] DateTime, [Fiscal Year] Text ( 255 ), [Forms]![frmMainMenu]![cboFacility] Text ( 255 ), [Forms]![frmMainMenu]![cboAdult/Juvenile] Text ( 255 );
TRANSFORM Count([Admissions by Facility and FY Inc Revoc].P_RegNo) AS [The Value]
SELECT [Admissions by Facility and FY Inc Revoc].facunit, sortmonth2([admit_date]) AS MonthOrder, [Admissions by Facility and FY Inc Revoc].rptmonth, Count([Admissions by Facility and FY Inc Revoc].P_RegNo) AS Total
FROM ([Admissions by Facility and FY Inc Revoc] INNER JOIN LkCtOrd_Facility ON [Admissions by Facility and FY Inc Revoc].faccode = LkCtOrd_Facility.CSBCode) INNER JOIN LkFacilityAbbrevs ON LkCtOrd_Facility.CSBCode = LkFacilityAbbrevs.Currfaccode
GROUP BY [Admissions by Facility and FY Inc Revoc].facunit, sortmonth2([admit_date]), [Admissions by Facility and FY Inc Revoc].rptmonth
ORDER BY sortmonth2([admit_date])
PIVOT [Admissions by Facility and FY Inc Revoc].LGroup In ("EVALN","NGRI","REVOC","DOC","JAIL","IST","URIST","OTHER");

There are about 7 sub queries. A coworker and I diagramed and compared each query. All returned the same results. Like is used throughout the database and uses the wildcard '*'. The only difference between all queries should be the use of form values vs. prompted values.

[CJ_London]

For xtabs, wherever you are using a parameter, be it the xtab itself or a referenced query, you must declare the parameters where they are referenced.

Per my last paragraph above, it is likely you have an error in your data - typically an unhandled null - if a control has not been completed, it will return a null value in your parameters which will generate the error you are getting.

in each of your queries you need to scan down each column where the parameter is applied looking for #error or similar, then take appropriate action to resolve it - usually by using the nz function. I would also check out your sortmonth2([admit_date]) function - include in the returned columns to check

Also recommend that where you are using like without a wildcard, change to =

[Paul H]

• We tried it both ways, with and without the parameters, same results.
• Like is necessary to run reports for multiple facilities.
• We tried to account for null, but got nowhere.

As stated the report and queries all ran in the old database. The only query that didn't run was the Xtab query, but we found a solution. To take out all the complexity from the Crosstab query we created a temp table with all the accumulated data in it, changed the SQL in the Xtab to read the table instead of the query and that did the trick. Here's the new SQL.

TRANSFORM Count(tblAdmissions_By_Facility_and_FY_Inc_Revoc.P_RegNo) AS [The Value]
SELECT tblAdmissions_By_Facility_and_FY_Inc_Revoc.facunit, sortmonth2([admit_date]) AS MonthOrder, tblAdmissions_By_Facility_and_FY_Inc_Revoc.rptmonth, Count(tblAdmissions_By_Facility_and_FY_Inc_Revoc.P_RegNo) AS Total
FROM tblAdmissions_By_Facility_and_FY_Inc_Revoc INNER JOIN (LkCtOrd_Facility INNER JOIN LkFacilityAbbrevs ON LkCtOrd_Facility.CSBCode = LkFacilityAbbrevs.Currfaccode) ON tblAdmissions_By_Facility_and_FY_Inc_Revoc.faccode = LkCtOrd_Facility.CSBCode
GROUP BY tblAdmissions_By_Facility_and_FY_Inc_Revoc.facunit, sortmonth2([admit_date]), tblAdmissions_By_Facility_and_FY_Inc_Revoc.rptmonth
ORDER BY sortmonth2([admit_date])
PIVOT tblAdmissions_By_Facility_and_FY_Inc_Revoc.LGroup In ("EVALN","NGRI","REVOC","DOC","JAIL","IST","URIST","OTHER");

My guess is all the form references triggered this, but don't know for sure. I'm content with the solution we have since I spent about 8 hours trying to revise a 1 page report.

I'm glad to be able to rant about this here. Helps keep me sane and may be helpful to someone else.

Time to move on.

Thanks for your help.

Paul