convert survey results

  • Thread starter Thread starter nycdon
  • Start date Start date
N

nycdon

I have survey data in following format (questions are actually column names)

[Clear Instructions] [Good Condition]....etc about 50 questions..as columns
Strongly Agree Agree
Agree Strongly Agree
Agree Agree
Neutral Agree

What is best route to get into format below..
Strongly Agree Agree Neutral
Clear Instructions 1 2 1
Good Condition 1 3 0


Thanks!
 
Use a union query then a crosstab.
qryQuestion --
SELECT "Clear Instructions" AS Question, [Clear Instructions] AS [Data]
FROM YourTable
UNION ALL SELECT "Good Condition" AS Question, [Good Condition] AS [Data]
FROM YourTable
.... ;

TRANSFORM Count(qryQuestion.Data) AS CountOfData
SELECT qryQuestion.Question
FROM qryQuestion
GROUP BY qryQuestion.Question
PIVOT [Data];
 
Thanks Karl!

Any way to easily get a total column across all responses?

KARL DEWEY said:
Use a union query then a crosstab.
qryQuestion --
SELECT "Clear Instructions" AS Question, [Clear Instructions] AS [Data]
FROM YourTable
UNION ALL SELECT "Good Condition" AS Question, [Good Condition] AS [Data]
FROM YourTable
.... ;

TRANSFORM Count(qryQuestion.Data) AS CountOfData
SELECT qryQuestion.Question
FROM qryQuestion
GROUP BY qryQuestion.Question
PIVOT [Data];

--
Build a little, test a little.


nycdon said:
I have survey data in following format (questions are actually column names)

[Clear Instructions] [Good Condition]....etc about 50 questions..as columns
Strongly Agree Agree
Agree Strongly Agree
Agree Agree
Neutral Agree

What is best route to get into format below..
Strongly Agree Agree Neutral
Clear Instructions 1 2 1
Good Condition 1 3 0


Thanks!
Click to expand...
Click to expand...
 
Can you give an example of what you mean by total across all responses?

Do you want the total count of responses for each question?
Or
Do you want a grand total of the number of the strongly agree, agree
neutral, etc for all the questions? There are 455 Totally agree
responses in the survey.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks Karl!

Any way to easily get a total column across all responses?

KARL DEWEY said:
Use a union query then a crosstab.
qryQuestion --
SELECT "Clear Instructions" AS Question, [Clear Instructions] AS [Data]
FROM YourTable
UNION ALL SELECT "Good Condition" AS Question, [Good Condition] AS [Data]
FROM YourTable
.... ;

TRANSFORM Count(qryQuestion.Data) AS CountOfData
SELECT qryQuestion.Question
FROM qryQuestion
GROUP BY qryQuestion.Question
PIVOT [Data];

--
Build a little, test a little.


nycdon said:
I have survey data in following format (questions are actually column names)

[Clear Instructions] [Good Condition]....etc about 50 questions..as columns
Strongly Agree Agree
Agree Strongly Agree
Agree Agree
Neutral Agree

What is best route to get into format below..
Strongly Agree Agree Neutral
Clear Instructions 1 2 1
Good Condition 1 3 0


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Try this --
TRANSFORM Count(qryQuestion.Data) AS CountOfData
SELECT qryQuestion.Question, Count(qryQuestion.Data) AS Total_Response
FROM qryQuestion
GROUP BY qryQuestion.Question
PIVOT [Data];

--
Build a little, test a little.


nycdon said:
Thanks Karl!

Any way to easily get a total column across all responses?

KARL DEWEY said:
Use a union query then a crosstab.
qryQuestion --
SELECT "Clear Instructions" AS Question, [Clear Instructions] AS [Data]
FROM YourTable
UNION ALL SELECT "Good Condition" AS Question, [Good Condition] AS [Data]
FROM YourTable
.... ;

TRANSFORM Count(qryQuestion.Data) AS CountOfData
SELECT qryQuestion.Question
FROM qryQuestion
GROUP BY qryQuestion.Question
PIVOT [Data];

--
Build a little, test a little.


nycdon said:
I have survey data in following format (questions are actually column names)

[Clear Instructions] [Good Condition]....etc about 50 questions..as columns
Strongly Agree Agree
Agree Strongly Agree
Agree Agree
Neutral Agree

What is best route to get into format below..
Strongly Agree Agree Neutral
Clear Instructions 1 2 1
Good Condition 1 3 0


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top