• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Setting column headings for crosstab query causes error

Experts,

I need to add a subform that is based on a crosstab query. This means I need to set the column headings in the query. However, when I do this, running the query produces the error message that says the expression is typed incorrectly or is too complex. It runs fine without the column headings being set.

At first I thought the problem may have something to do with the column headings being numeric, but changing them to text did not help.

Thanks, Dale

PARAMETERS [TempVars]![SelectedCrop] Long, [TempVars]![SelectedCountry] Long, [TempVars]![SelectedDriver] Long;
TRANSFORM Avg(qryExpertOpinionSub1.Decrease) AS AvgOfDecrease
SELECT tblProductMainGroups.ProductMainGroup AS Product, tblClasses.Class
FROM tblClasses INNER JOIN ((qryExpertOpinionSub2 LEFT JOIN qryExpertOpinionSub1 ON (qryExpertOpinionSub2.CropYear = qryExpertOpinionSub1.CropYear) AND (qryExpertOpinionSub2.ProductMainGroupID = qryExpertOpinionSub1.ProductMainGroup) AND (qryExpertOpinionSub2.ClassID = qryExpertOpinionSub1.ClassID)) INNER JOIN tblProductMainGroups ON qryExpertOpinionSub2.ProductMainGroupID = tblProductMainGroups.ProductMainGroupID) ON tblClasses.ClassID = qryExpertOpinionSub2.ClassID
GROUP BY tblProductMainGroups.ProductMainGroup, tblClasses.Class
PIVOT qryExpertOpinionSub2.CropYear In ("Product","Class",2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025);

Open in new window

0
Dale Logan
Asked:
Dale Logan
  • 2
1 Solution
 
Dale FyeCommented:
Remove the columns "Product" and "Class" from the PIVOT IN () clause

PIVOT qryExpertOpinionSub2.CropYear In (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025);
0
 
Dale LoganConsultantAuthor Commented:
Duh..."column" heads. I've done it correctly before, just been a long time.
0
 
Dale FyeCommented:
glad I could help.  We've all been there!  ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now