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

[Audrey1980]

Hi, I am trying to create a report using a crosstab query as the source. I
have only added the first few columns and rows and tried to see it in layout
format but i am getting the following issue "cannot use the crosstab of a
non-fixed column as a query". I have added potential test headings to the
column heading field in the sql query view design and comma delimited the
options but this is stil not working.

Any ideas? Thanks.

 

[Jerry Whittle]

Show us the SQL of the crosstab query with the parameters. Open the query in
design view. Next go to View, SQL View and copy and past it here.

 

[Audrey1980]

Jerry

Here's the SQL - thanks:

PARAMETERS [Forms]![Main Menu]![RSM] Text ( 255 ), [Forms]![Main
Menu]![Week] Short, [Forms]![Main Menu]![Year] Short;
TRANSFORM
IIf(Sum([VALUEAMOUNTEXCVAT])=0,0,Sum([MARGIN])/Sum([VALUEAMOUNTEXCVAT])) AS
[margin %]
SELECT [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
FROM (VALIDDATABODY LEFT JOIN [RSM STORE LISTING] ON
VALIDDATABODY.storeNumber = [RSM STORE LISTING].Store) LEFT JOIN [MSR
HIERARCHY] ON (VALIDDATABODY.MSRSubDepartment = [MSR
HIERARCHY].MSRSubDepartment) AND (VALIDDATABODY.MSRDepartment = [MSR
HIERARCHY].MSRDepartment)
WHERE ((([RSM STORE LISTING].RSM)=[Forms]![Main Menu]![RSM]) AND
((VALIDDATABODY.week)=[Forms]![Main Menu]![Week]) AND
((VALIDDATABODY.year)=[Forms]![Main Menu]![Year]))
GROUP BY [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
ORDER BY VALIDDATABODY.MSRDepartment, VALIDDATABODY.MSRSubDepartment
PIVOT [RSM STORE LISTING].[Store Name] In ("storea","storeb","store c");

 

[Jerry Whittle]

Hi,

I don't see anything obvious. I suggest that you create a query to gather up
all the information including the IIf and criteria. Make sure that it returns
the proper records. Next base a crosstab on the first query. Include the
column headings. Then create a new form or report based on this second
crosstab query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry

Here's the SQL - thanks:

PARAMETERS [Forms]![Main Menu]![RSM] Text ( 255 ), [Forms]![Main
Menu]![Week] Short, [Forms]![Main Menu]![Year] Short;
TRANSFORM
IIf(Sum([VALUEAMOUNTEXCVAT])=0,0,Sum([MARGIN])/Sum([VALUEAMOUNTEXCVAT])) AS
[margin %]
SELECT [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
FROM (VALIDDATABODY LEFT JOIN [RSM STORE LISTING] ON
VALIDDATABODY.storeNumber = [RSM STORE LISTING].Store) LEFT JOIN [MSR
HIERARCHY] ON (VALIDDATABODY.MSRSubDepartment = [MSR
HIERARCHY].MSRSubDepartment) AND (VALIDDATABODY.MSRDepartment = [MSR
HIERARCHY].MSRDepartment)
WHERE ((([RSM STORE LISTING].RSM)=[Forms]![Main Menu]![RSM]) AND
((VALIDDATABODY.week)=[Forms]![Main Menu]![Week]) AND
((VALIDDATABODY.year)=[Forms]![Main Menu]![Year]))
GROUP BY [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
ORDER BY VALIDDATABODY.MSRDepartment, VALIDDATABODY.MSRSubDepartment
PIVOT [RSM STORE LISTING].[Store Name] In ("storea","storeb","store c");

Show us the SQL of the crosstab query with the parameters. Open the query in
design view. Next go to View, SQL View and copy and past it here.