I have created a report that has a list of actions which are populated from an "Actions" table. (See Below)

enter image description here

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)

enter image description here

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 WHERE criteria.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.