Crosstab query as record source of report

[davidcantor]

When I run a report with the source as a crosstab query i get an error message:

"cannot use the crosstab of a non-fixed column as a sub query"

what does this mean?

[davidcantor]

this is my query:

TRANSFORM Sum(M_EExpensesDetails.ExpenseAmount) AS SumOfExpenseAmount
SELECT [M_Info].[Fname] & " " & [M_Info].[Lname] AS Ename
FROM M_EExpensesDetails INNER JOIN M_Info ON M_EExpensesDetails.ID = M_Info.ID
GROUP BY [M_Info].[Fname] & " " & [M_Info].[Lname]
PIVOT M_EExpensesDetails.ExpenseType;

[HansV]

The column headings of a crosstab query can in theory change over time, if the values of the column heading field changes.
A report, on the other hand, has fixed control sources for its text boxes etc.
There are two ways around this:

1) If the column headings actually won't change, you can fix them by specifying the Column Headings property of the crosstab query:

S0337.png

You can enter the column headings here in the order that you want them to appear, separated by commas (and enclosed in quotation marks if necessary).

2) If the column headings may change, you need to create a dynamic crosstab report; this requires quite complicated VBA code.

[HansV]

The SQL for the query with fixed column headings would be

TRANSFORM Sum(M_EExpensesDetails.ExpenseAmount) AS SumOfExpenseAmount
SELECT [M_Info].[Fname] & " " & [M_Info].[Lname] AS Ename
FROM M_EExpensesDetails INNER JOIN M_Info ON M_EExpensesDetails.ID = M_Info.ID
GROUP BY [M_Info].[Fname] & " " & [M_Info].[Lname]
PIVOT M_EExpensesDetails.ExpenseType In ("Travel", "Lodging", "Food")

The categories "Travel" etc. are just examples of course.