If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Counting rows in a Query | Janet | General Discussion | 9 | January 9th, 2005 05:13 PM |
Ideas for macro to add row for count and percentage | Bradley C. Hammerstrom | Tables | 8 | January 6th, 2005 10:38 AM |
Count Consecutive Months - Repost | Steve | Running & Setting Up Queries | 1 | September 16th, 2004 04:47 PM |
count individuals of a certain sizes in one query | jw | Running & Setting Up Queries | 4 | June 23rd, 2004 08:16 PM |