Re: Count the no of replies.
- From: "John Spencer (MVP)" <spencer4@xxxxxxxxxxx>
- Date: Sat, 16 Apr 2005 18:37:28 -0400
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
> >
.
- References:
- Count the no of replies.
- From: FC
- Re: Count the no of replies.
- From: John Spencer (MVP)
- Re: Count the no of replies.
- From: FC
- Count the no of replies.
- Prev by Date: Access queries failing after transfer to XP
- Next by Date: Re: Access queries failing after transfer to XP
- Previous by thread: Re: Count the no of replies.
- Next by thread: database gets placed in a State that prevents it from being opened
- Index(es):
Relevant Pages
|
