Crosstab to convert row value into column

[foxtrot123]

I have a survery database with data stored accordingly:

tblSubjects stores info on the subject (e.g., SubjectID, Name)
tblQuestions stores into on the questions (e.g., QstnTXT)
tblResponses stores info on the responses (e.g., RspnsID, Rspns)

I use a query to pull together data from all three tables to show responses like so:

RspnsID SubjectID QstnTxt       Rspns
1        1       Gender         Male
2        1       FavoriteColor Red
3        1       FavoriteColor Green
----------------------------------------
4        2       Gender         Female
5        2      FavoriteColor   Green
etc.

I'd like to create an Access pivot table to show, e.g., the count of favorite colors by Gender.

To do this I think I need to create a new variable called Gender, and restructure the data like so:

RspnsID SubjectID QstnTxt       Rspns    Gender
----------------------------------------------
2      1         FavoriteColor    Red       Male
3      1         FavoriteColor    Green   Male
----------------------------------------------
5      2      FavoriteColor    Green   Female
etc.

In the the pivot table, Gender would be my Column variable, Rspns would be my Row Variable, and SubjectID would be my Count variable.

I know how to make a crosstab to convert all the questions into columns, but for this situation I need to do it only when the QstnTxt = "Gender".

Any suggestions about how to do this?

 

[scubadiver007]

I get the distinct feeling your tables aren't normalised

 

[foxtrot123]

I get the distinct feeling your tables aren't normalised

They are normalized. For example:

tblSubjects stores info on the subject (e.g., SubjectID, Name)
tblQuestions stores info on the questions (e.g., QstnTXT)
tblAnswers stores possible answers for each question.
tblResponses stores info on actual responses (e.g., RspnsID, Rspns)
etc.

What I'm trying to do is *de*normalize some of it to facilitate the pivot table issue.