Transform some rows to columns

[foxtrot123]

I can use a simple query that shows survey responses like so:

SubID QstnBrief Rspns
--------------------
1     Gender  Male
1     Color   Red
1     Color   Blue
1     Color   Orange
--------------------
2     Gender  Female
2     Color   Blue
--------------------
etc.

Gender = What is you gender?
Color = What is favorite color(s)? (select all that apply)

Data on subjects is stored in tblSubjects.
Data on questions (QstnID, QstnBrief, etc.) is stored in tblQuestions. This table includes a variable to indicate of the question is a 'select all that apply' question.
Data on responses is stored in tblResponses.

I would like to convert these data to this structure:

SubID Gender Color_Red Color_Blue Color_Orange
1     Male   Red       Blue       Orange
2     Female           Blue       
etc.

In other words, the 'select all that apply questions" (like Color) need to return a separate variable for each response (using QstnBrief & "_" & Rspns).

If I didn't have 'select all that apply' questions, I could just use a simply crosstab to convert the data:

TRANSFORM First(tblResponses.Rspns) AS FirstOfRspns
SELECT tblSubjects.SubID AS SubID
FROM tblSubjects INNER JOIN (tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID = tblResponses.QstnID) ON tblSubjects.SubID = tblResponses.RspnsID
GROUP BY tblSubjects.SubID
PIVOT tblQuestions.QstnBrief;

But this doesn't handle the select all that apply questions the way I need. Suggestions?

 

[foxtrot123]

Figured it out. Turned out to be quite simple. Solution attached.

Basically:

1. Create a select query to pull the data together. Include in it a calculated field, MergedVar, with this formula: IIf([QstnType]="SelectAll",[QstnBrief] & "_" & [Rspns],[QstnBrief]) AS MergedVarr

SELECT tblSubjects.SubID, tblQuestions.QstnBrief, tblResponses.Rspns, IIf([QstnType]="SelectAll",[QstnBrief] & "_" & [Rspns],[QstnBrief]) AS MergedVarr
FROM (tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID = tblResponses.QstnID) INNER JOIN tblSubjects ON tblResponses.SubID = tblSubjects.SubID;

This returns:

SubID QstnBrief Rspns MergedVar
------------------------------- 
1     Gender  Male    Male 
1     Color   Red     Color_Red 
1     Color   Blue    Color_Blue 
1     Color   Orange  Color_Orange 
------------------------------- 
2     Gender  Female  Female 
2     Color   Blue    Color_Blue 
-------------------------------

2. Create a crosstab query based off the query above:

TRANSFORM First(Query1.Rspns) AS FirstOfRspns
SELECT Query1.SubID
FROM Query1
GROUP BY Query1.SubID
PIVOT Query1.MergedVarr;