I want to use the result of a crosstab with a flexible amount of column headings in a form. The query shows how many hours employees (column headings) spent on different tasks (row headings).
The query works if I open it as is. Pulling it into the form I get the error message that I cannot use a crosstab query without fixed column headings. I need the ability to show a flexible amount of column headings since otherwise the resulting table is too big and confusing. Is there a solution for this problem?
That's the query:
PARAMETERS [Forms]![PL_Stundenabfrage]![ProjektWaehler] Long;
TRANSFORM Round(Sum(DateDiff("n",[tblZeiten].[ZE_Start],[tblZeiten].[ZE_Ende])/60),2) AS Stunden
SELECT [KS_Bez] & " (" & [KS_BezLang] & ")" AS Taetigkeit, Sum(Stunden) AS GESAMT
FROM tblMa INNER JOIN (tblKst INNER JOIN (tblPr INNER JOIN tblZeiten ON tblPr.PR_Key = tblZeiten.ZE_Pr) ON tblKst.KS_Key = tblZeiten.ZE_Kst) ON tblMa.MA_ISN = tblZeiten.ZE_MA
WHERE (((tblZeiten.ZE_Pr)=[Forms]![PL_Stundenabfrage]![ProjektWaehler]))
GROUP BY [KS_Bez] & " (" & [KS_BezLang] & ")"
PIVOT tblMa.MA_Nachname;