Crosstab Query with two values

[Lkwdmntr]

Hello,

I am trying to create a crosstab query that has two values under the cross tab columns. I am attaching an excel file that shows what I am looking to accomplish. The first sheet has the data and how it comes up in the query I made and the second sheet shows how I am hoping to get the new query result.

 

[June7]

Review http://allenbrowne.com/ser-67.html#MultipleValues

 

[Pat Hartman]

A crosstab query can only pivot a single column at a time. If you want to do multiples, create a separate crosstab for each column then create a final query that joins all the crosstabs. You will have to alias the columns as you select them because all the crosstabs will have the same column name.

 

[Lkwdmntr]

Having a hard time grasping this. I did make the two separate crosstab queries, but not sure how to combine them. I uploaded a new cutdown version of my database on google drive.

FettlerHealthDBCutDownCopy.accdb

drive.google.com

 

[June7]

As long as the two queries have exactly same User, Index, Action values, join them.

SELECT Planned_Crosstab.UserID, Planned_Crosstab.Index, Planned_Crosstab.Action, Actual_Crosstab.[1], Actual_Crosstab.[2], Actual_Crosstab.[3], Actual_Crosstab.[4], Actual_Crosstab.[5], Actual_Crosstab.[6], Actual_Crosstab.[7], Actual_Crosstab.[8], Actual_Crosstab.[9], Actual_Crosstab.[10], Actual_Crosstab.[11], Actual_Crosstab.[12], Planned_Crosstab.[1], Planned_Crosstab.[2], Planned_Crosstab.[3], Planned_Crosstab.[4], Planned_Crosstab.[5], Planned_Crosstab.[6], Planned_Crosstab.[7], Planned_Crosstab.[8], Planned_Crosstab.[9], Planned_Crosstab.[10], Planned_Crosstab.[11], Planned_Crosstab.[12]
FROM Actual_Crosstab INNER JOIN Planned_Crosstab ON (Actual_Crosstab.Action = Planned_Crosstab.Action) AND (Actual_Crosstab.Index = Planned_Crosstab.Index) AND (Actual_Crosstab.UserID = Planned_Crosstab.UserID);