counting the number of responses

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm not sure if this is possible but i have a table complete with responses
from a questionnaire, the possible reponses are mostly as follows; Very Good,
Good, Average, Bad, Very Bad. etc.....

My table is set out with the unique identifier as the row header and the
question number as each column heading and the reponse given (in text) as the
value. Therefore, is there a way i can count the number of response for each
question, eg.. for question 1 the number that said good, the number that said
bad, etc..... and so on for about 40 questions.

The only way i can think of is an individual crosstab query for every
question, but is there another way.....

Thanks in Advance
 
You could try normalize your table structure with a union query. Then, make
a totals query based on the union query:

SELECT 1 as Question, [Q1] as Response
FROM tblQuestionniare
UNION ALL
SELECT 2, [Q2]
FROM tblQuestionnaire
UNION ALL
....;

Then create your totals query
SELECT Question, Response, Count(Response) as NumOf
FROM quniYourQuery
GROUP BY Question, Response;
 
thanks but i might be bieng daft here but i put in the following SQl code but
get an error message saying "Invalid SQL statement; expected 'Delete',
'Insert', 'Procedure', 'Select', or 'update'

SELECT 1 as Question, [10a] as Response
FROM [All Data - Searched]
UNION ALL
SELECT 2, [10b]
FROM [All Data - Searched]
UNION ALL;

where questions 10a and 10b are the first two questions from a table called
'All Data - Searched'
what am i doing wrong????????????

Duane Hookom said:
You could try normalize your table structure with a union query. Then, make
a totals query based on the union query:

SELECT 1 as Question, [Q1] as Response
FROM tblQuestionniare
UNION ALL
SELECT 2, [Q2]
FROM tblQuestionnaire
UNION ALL
....;

Then create your totals query
SELECT Question, Response, Count(Response) as NumOf
FROM quniYourQuery
GROUP BY Question, Response;

--
Duane Hookom
MS Access MVP
--

sdg8481 said:
Hi,

I'm not sure if this is possible but i have a table complete with
responses
from a questionnaire, the possible reponses are mostly as follows; Very
Good,
Good, Average, Bad, Very Bad. etc.....

My table is set out with the unique identifier as the row header and the
question number as each column heading and the reponse given (in text) as
the
value. Therefore, is there a way i can count the number of response for
each
question, eg.. for question 1 the number that said good, the number that
said
bad, etc..... and so on for about 40 questions.

The only way i can think of is an individual crosstab query for every
question, but is there another way.....

Thanks in Advance
Click to expand...
Click to expand...
 
The "UNION ALL" goes between SELECT statements, but not at the end. You
are permitted to include an ORDER BY, but it must use field names from
the first SELECT. Read the Help information on Union Queries for info
on syntax. I think your Query will work if you delete the last "UNION ALL".

What Duane Hookom suggested was including one SELECT for each of the
repeated fields in your original Table, and I assume you're doing just
the first 2 as a test.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
thanks but i might be bieng daft here but i put in the following SQl code but
get an error message saying "Invalid SQL statement; expected 'Delete',
'Insert', 'Procedure', 'Select', or 'update'

SELECT 1 as Question, [10a] as Response
FROM [All Data - Searched]
UNION ALL
SELECT 2, [10b]
FROM [All Data - Searched]
UNION ALL;

where questions 10a and 10b are the first two questions from a table called
'All Data - Searched'
what am i doing wrong????????????

:

You could try normalize your table structure with a union query. Then, make
a totals query based on the union query:

SELECT 1 as Question, [Q1] as Response
FROM tblQuestionniare
UNION ALL
SELECT 2, [Q2]
FROM tblQuestionnaire
UNION ALL
....;

Then create your totals query
SELECT Question, Response, Count(Response) as NumOf
FROM quniYourQuery
GROUP BY Question, Response;

--
Duane Hookom
MS Access MVP
--

Hi,

I'm not sure if this is possible but i have a table complete with
responses
from a questionnaire, the possible reponses are mostly as follows; Very
Good,
Good, Average, Bad, Very Bad. etc.....

My table is set out with the unique identifier as the row header and the
question number as each column heading and the reponse given (in text) as
the
value. Therefore, is there a way i can count the number of response for
each
question, eg.. for question 1 the number that said good, the number that
said
bad, etc..... and so on for about 40 questions.

The only way i can think of is an individual crosstab query for every
question, but is there another way.....

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
Thanks Vincent. I often leave too many assumptions in my posts. I did indeed
infer the union statement should repeat for each field. The end of the SQL
should be FROM yourtablename or the order by.

--
Duane Hookom
MS Access MVP
--

Vincent Johns said:
The "UNION ALL" goes between SELECT statements, but not at the end. You
are permitted to include an ORDER BY, but it must use field names from the
first SELECT. Read the Help information on Union Queries for info on
syntax. I think your Query will work if you delete the last "UNION ALL".

What Duane Hookom suggested was including one SELECT for each of the
repeated fields in your original Table, and I assume you're doing just the
first 2 as a test.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
thanks but i might be bieng daft here but i put in the following SQl code
but get an error message saying "Invalid SQL statement; expected
'Delete', 'Insert', 'Procedure', 'Select', or 'update'

SELECT 1 as Question, [10a] as Response
FROM [All Data - Searched]
UNION ALL
SELECT 2, [10b]
FROM [All Data - Searched]
UNION ALL;

where questions 10a and 10b are the first two questions from a table
called 'All Data - Searched'
what am i doing wrong????????????

:

You could try normalize your table structure with a union query. Then,
make a totals query based on the union query:

SELECT 1 as Question, [Q1] as Response
FROM tblQuestionniare
UNION ALL
SELECT 2, [Q2]
FROM tblQuestionnaire
UNION ALL
....;

Then create your totals query
SELECT Question, Response, Count(Response) as NumOf
FROM quniYourQuery
GROUP BY Question, Response;

--
Duane Hookom
MS Access MVP
--


Hi,

I'm not sure if this is possible but i have a table complete with
responses
from a questionnaire, the possible reponses are mostly as follows; Very
Good,
Good, Average, Bad, Very Bad. etc.....

My table is set out with the unique identifier as the row header and the
question number as each column heading and the reponse given (in text)
as the
value. Therefore, is there a way i can count the number of response for
each
question, eg.. for question 1 the number that said good, the number that
said
bad, etc..... and so on for about 40 questions.

The only way i can think of is an individual crosstab query for every
question, but is there another way.....

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top