Count the no of replies.

I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately. How
should I do this, in a query? Pls Help!!!!!
Thanks

Select questionNumber, response, count(*) from questions group by
questionNumber, response

"FC" wrote in message
...
I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately.
How
should I do this, in a query? Pls Help!!!!!
Thanks

Pretty hard to say without knowing what your tables look like.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"FC" wrote in message
...
I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately.
How
should I do this, in a query? Pls Help!!!!!
Thanks

ID Name This is your first visit
1 Carol Riaz 0
2 1
3 0
4 Robinson Newton 1
5 1
6 1
Above is a table I could copy for you to take a look at. In the last column,
the question is "Is this your first visit?" 1= YES
0= NO
N/A
This is just one of the questions, so how do I create a query to count the
no of 1's, 0's and N/A's?
Thanks
FC

"Douglas J. Steele" wrote:

Pretty hard to say without knowing what your tables look like.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"FC" wrote in message
...
I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately.
How
should I do this, in a query? Pls Help!!!!!
Thanks

Not easily since your structure is not normalized.

SELECT Abs(Sum(Question1="Yes")) as Q1Y,
Abs(Sum(Question1="No")) as Q1N,
Abs(Sum(Question1="N/A")) as Q1NA,
Abs(Sum(Question2="Yes")) as Q2Y,
....
FROM YourTable

If you are doing this in the query grid.
Enter in the field cell

Field: Q1Y: Abs(Sum(Question1="Yes"))

You might have to select Totals from the View Menu and then select Expression
for each total.


FC wrote:

I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately. How
should I do this, in a query? Pls Help!!!!!
Thanks

How do I normalize the structure? Also where in Design Query will the
statements go that u have provided?
Thanks for your help.
FC

"John Spencer (MVP)" wrote:

Not easily since your structure is not normalized.

SELECT Abs(Sum(Question1="Yes")) as Q1Y,
Abs(Sum(Question1="No")) as Q1N,
Abs(Sum(Question1="N/A")) as Q1NA,
Abs(Sum(Question2="Yes")) as Q2Y,
....
FROM YourTable

If you are doing this in the query grid.
Enter in the field cell

Field: Q1Y: Abs(Sum(Question1="Yes"))

You might have to select Totals from the View Menu and then select Expression
for each total.


FC wrote:

I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately. How
should I do this, in a query? Pls Help!!!!!
Thanks

John,
I am trying to send the table again

ID Name Is this is your first visit
1 Carol Riaz 0
2 1
3 0
4 Robinson Newton 1
5 1
6 1
7 Julius Rose 0
8 Georgina Adams 1
9 James Phillips 1
10 0
11 Peter Morris 1
12 Marion Haugen 1
13 1
14 1
15 0

As I said 1=Yes; 0=No
I do the query through the Design Query Wizard, so how do I get a count for
the no of Yes and the no of No,s for this question. also there are quiet a
few more questions with similar results, so will I have to write a query for
each question separately?
Thanks for your help.
FC



"John Spencer (MVP)" wrote:

Not easily since your structure is not normalized.

SELECT Abs(Sum(Question1="Yes")) as Q1Y,
Abs(Sum(Question1="No")) as Q1N,
Abs(Sum(Question1="N/A")) as Q1NA,
Abs(Sum(Question2="Yes")) as Q2Y,
....
FROM YourTable

If you are doing this in the query grid.
Enter in the field cell

Field: Q1Y: Abs(Sum(Question1="Yes"))

You might have to select Totals from the View Menu and then select Expression
for each total.


FC wrote:

I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately. How
should I do this, in a query? Pls Help!!!!!
Thanks

You will need separate columns for each question plus answer response.

In the grid you will need to do the following. Assuming that your field name is
[Is This Your First Visit]

Field: FirstVisitYes: Abs(SUM([Is This Your First Visit]=1))

Field: FirstVisitNo: Abs(Sum([Is This Your First Visit]=0))

Field: MotherLivingYes: Abs(Sum([Is your mother alive]=1))

Field: MotherLivingNo: Abs(Sum([Is your mother alive]=0))

What you should have for a structure is more like:

ResponsesByPeople (Table)
PersonID
QuestionID
Response

Questions (Table)
QuestionID
QuestionText

People(Table)
PersonID
PersonName

Then it would be really simple to get your data out in a Totals query (aka
Summary Query or Aggregate query) or in a Crosstab query. Something like:
SELECT Q.QuestionText, R.Response, Count(R.Response) as CountAnswers
FROM Questions as Q INNER JOIN ResponsesByPeople as R
On Q.QuestionID = R.QuestionID
GROUP BY Q.QuestionText

That would return data like
Question1 asked ... 0 52
Question1 asked ... 1 12
Question2 asked ... 0 18
Question2 asked ... 1 21


You can try to write a union query to normalize your data, but that cannot be
done in the query grid. You must use the SQL (TEXT) view to do so.

SELECT "Is this your First Visit" as QuestionText, [Is This Your First Visit] as Response
UNION ALL
SELECT "Is your Mother alive", [Is your Mother alive]
UNION ALL
SELECT ...

Then you can write your totals query against that saved query

SELECT QuestionText, Response, Count(Response) as CountAnswers
FROM TheSavedUnionQuery
GROUP BY QuestionText, Response

That should return responses and counts similar to the above.



FC wrote:

John,
I am trying to send the table again

ID Name Is this is your first visit
1 Carol Riaz 0
2 1
3 0
4 Robinson Newton 1
5 1
6 1
7 Julius Rose 0
8 Georgina Adams 1
9 James Phillips 1
10 0
11 Peter Morris 1
12 Marion Haugen 1
13 1
14 1
15 0

As I said 1=Yes; 0=No
I do the query through the Design Query Wizard, so how do I get a count for
the no of Yes and the no of No,s for this question. also there are quiet a
few more questions with similar results, so will I have to write a query for
each question separately?
Thanks for your help.
FC

"John Spencer (MVP)" wrote:

Not easily since your structure is not normalized.

SELECT Abs(Sum(Question1="Yes")) as Q1Y,
Abs(Sum(Question1="No")) as Q1N,
Abs(Sum(Question1="N/A")) as Q1NA,
Abs(Sum(Question2="Yes")) as Q2Y,
....
FROM YourTable

If you are doing this in the query grid.
Enter in the field cell

Field: Q1Y: Abs(Sum(Question1="Yes"))

You might have to select Totals from the View Menu and then select Expression
for each total.


FC wrote:

I have a form which is a survey, with questions as labels and against each
question is a combo box with the following replies to choose from: "YES",
"NO" and "N/A".
I would like to generate a query to count the no of "YES" replies, no. of
"NO" replies and the no. of "N/A" replies for each question separately. How
should I do this, in a query? Pls Help!!!!!
Thanks