Trying to create crosstab result

[glxman]

Hi

I have a set of data where the the first column header is a reference to respondent names (Respondent ID) and all the other columns are references to questions, ie Q1, Q2, Q3, Q4, etc. The data under these question headers is the response to that question from the respondent, which will either be Strongly Agree, Agree, Disagree, Strongly Disagree.


I want to transform this so the headers are, Question ID, Strongly Agree, Agree, Disagree, Strongly Disagree with a count of the responses.

For some reason I cant get my head around it. Any advice greatly appreciated.

Many thanks
Richard

 

[Crowley16]

set up a questions table where you have a list of questionIDs

then join your original table with this new lookup table on your first question to get the result of the question, repeat this process using unions for each of your questions...

e.g.

select questionID, result
from tblQuestion as Q inner join tblLookUp as L on Q.Q1 = L.questionID

union select questionID, result
from tblQuestion as Q inner join tblLookUp as L on Q.Q2 = L.questionID

...

for the next stage, you can use the query you have created above and iif statements to split out your responses to each various level, or you can just use counts to work out numbers of each response...

--------------------
Procrastinate Now!

 

[glxman]

Hi

Thanks for your reply - I had already created a questions ID table as I thought I would need it. To simplify: I've got a tblQuestions table which relates the headers from the tblSurvey table

tblQuestions has the following:

Question ID
Question

tblSurvey has the following:

Respondent ID
Q1
Q2
Q3
Q4
Q5
Q6
Q7
(etc. to 50)

Can I ask you what the exact SQL would be for the first stage please as I am not too sure what you mean. I tried modifying your SQL but without success.

Many thanks
Richard

 

[dhookom]

I think your table structure is wrong. You might want to take a look at a normalized questionaire/survey application "At Your Survey"

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You could take your structure and normalize it with a massive union query and then build a crosstab based on the union query. 49 UNIONs might be more than Access can handle.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[glxman]

Hi thanks, I guessed that the table structure was wrong. I was given the survey results like this in Excel format and was asked to analyse. I just want to now transform it so I am able to do that.

Is there a way I can get this into a workable format?

Many thanks
Richard

 

[dhookom]

You could take your structure and normalize it with a massive union query and then build a crosstab based on the union query. 49 UNIONs might be more than Access can handle.

You could try SQL like:

SELECT ID, Q1 as Answer, 1 as Question
FROM tblSurvey
UNION ALL
SELECT ID, Q2, 2
FROM tblSurvey
UNION ALL
SELECT ID, Q3, 3
FROM tblSurvey
UNION ALL
SELECT ID, Q4, 4
FROM tblSurvey
--- etc ---
UNION ALL
SELECT ID, Q50, 50
FROM tblSurvey;

If this is too complex, I would create a normalized table structure and use code to loop through your current table and append to the normalized table.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]