Cannot use the crosstab of a non-fixed column as a subquery

[topanga37]

hello!

I am very new to this, (excel wizard, but access not so much) but I am trying to put together an access program that tracks the name, date, and two scores of a product, i.e. John Smith, 5/11/2011, 3 for relevancy, 2 for timeliness. These individuals are assigned a group, i.e. morning crew, mid-shift, and night-shift. I have created queries for the individuals average points for relevancy and timeliness per month - but I am having a lot of problems doing it for the groups / shifts. Right now I have a table that lists out the name, date, score for relevancy, score for timeliness; a query that lists out the total number of products per group (columns are the months); a query that lists out the total points per group (columns are the months). I attempted to create a final query to get the monthly average of the groups scores (column a = group, column b = total points for relevancy, columb c = avg relevancy points), but everytime I do it comes up with the error: Cannot use the crosstab of a non-fixed column as a subquery. Help!! Below is my SQL for the query ...

TRANSFORM Sum([SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon].Apr) AS SumOfApr
SELECT [Short Burst Metrics Data_Crosstab_Division / Branch].[Division / Branch], ([SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon]![Apr]/[Short Burst Metrics Data_Crosstab_Division / Branch]![Apr]) AS [Avg of Relevancy Pnts], Sum(Nz([SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon].[Apr],0)) AS [Total Of Relevancy Pnts]
FROM [Short Burst Metrics Data_Crosstab_Division / Branch] LEFT JOIN [SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon] ON [Short Burst Metrics Data_Crosstab_Division / Branch].[Division / Branch]=[SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon].[Division / Branch]


GROUP BY [Short Burst Metrics Data_Crosstab_Division / Branch].[Division / Branch], ([SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon]![Apr]/[Short Burst Metrics Data_Crosstab_Division / Branch]![Apr])
PIVOT [SB Metrics Data_Crosstab_Relevancy_Division / Branch_Mon].[Total Relevancy Pnts];


Thank you!!

[kennejd]

I'm a little confused by your post....but to use crosstabs in subqueries you have to specify the Column Headings in your crosstab property sheet. Here's an example of headings I used in a crosstab query to display dates:

'2010/04','2010/05','2010/06','2010/07','2010/08','2010/09','2010/10','2010/11','2010/12','2011/01','2011/02','2011/03'