crosstab/totals query

[vgnmnky]

Hi
I would be grateful for a little guidance or a nudge in the right direction on this. I haven't got a lot of experience using SQL but I'm keen to learn..

I've built a database that collects some questionnaire results. Each question has a field in a table (eg, Q1, Q2) and there are 6 possible responses (0-5 (0 no, response, 1 strongly agree- 5 strongly disagree))

I'd like to pull the data out of this so I can see the totals for each response for each question, something like this:
0 1 2 3 4 5

Q1 8 3 2 1 5 0
Q2 1 2 5 3 0 8
Q3 etc

Then I should be able to create charts to display the data in a way that makes our department look good!

Unfortunately, my brain is completely stuck on how to begin...I've played around with a few ideas but failing miserably..any ideas appreciated!

Thanks,
Rob

 

[M8KWR]

a croiss tab should do what you need.

What i have done is to create a table with the following

ID (Primary Key)
Question (Text)
Answer (Number)

I saved the table as Table1

Then use this code

TRANSFORM Count(Table1.ID) AS [The Value]
SELECT Table1.Question, Count(Table1.ID) AS [Total Of ID]
FROM Table1
GROUP BY Table1.Question
PIVOT Table1.Answer;

Hope this helps

 

[dhookom]

You might first need to normalize your table with a union query:

SELECT "Q1" as Question, Q1 as Answer
FROM tblNoNameGiven
UNION ALL
SELECT "Q2", Q2
FROM tblNoNameGiven
UNION ALL
SELECT "Q3", Q3
FROM tblNoNameGiven
...

You can then create a crosstab from the results of the union query.


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]

 

[vgnmnky]

Thanks for your replies. I haven't had a chance to try them out yet, but I'll let you know how I go.

Thanks again,

Rob

 

[dhookom]

You might want to look at the sample survey application "At Your Survey" found at

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

This survey is a normalized solution that allows a person to create, enter results, and report without having to change a table structure, form,....

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]