I have created a report that has a list of actions which are populated from an "Actions" table. (See Below)
However, each action will have multiple updates populated from an "Updates" tables and I need the updates to be pivoted as there is a common field between them all which is the Date of the Update. (See Below)
As you can see, I have managed to complete this as there are 2 updates in the sub table. However, I have had to manually stipulate the
WHERE criteria because I am unable to assign the main report field "ID" (The fields under the Action Column are named ID in the main report) to the
My SQL code for what I have at the moment is:
TRANSFORM First(Updates.[Description]) AS FirstOfDescription SELECT Updates.[ActionID] FROM Updates WHERE Updates.ActionID=2 GROUP BY Updates.[ActionID] PIVOT Format([UpdateDate],"Short Date");
But I would assume all I would have to do is change the
WHERE line so that it reads the following:
TRANSFORM First(Updates.Description) AS FirstOfDescription SELECT Updates.ActionID FROM Updates WHERE (((Updates.ActionID)=[ActionPlanView].[ID])) GROUP BY Updates.ActionID PIVOT Format([UpdateDate],"Short Date");
NOTE: ActionPlanView is the name of the parent report where the sub report with the pivot table will be embedded.
But when I try this I get the following error message:
The Microsoft Access database engine does not recognize '[ActionPlanView].[ID]' as a valid name or expression
What am I doing wrong? I just want the sub report to be filtered on the Action number (Field "ID") of the parent report.
I only appear to get this issue when I attempt to transpose the data (Pivot). If I leave it as the following SQL statement it works but it is not in the correct format:
SELECT Updates.ActionID, Updates.UpdateDate, Updates.Description FROM Updates WHERE (((Updates.ActionID)=[ActionPlanView].[ID]));
I am happy to provide more information if required but I am rather new to Databases so I apologies in advance if I am missing something.