counting

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a form that has multiple questions on it. The
first 5 are all that need to be counted. In these first 5
the response is either Yes, No, or NA. These 5 questions
are part of one record. I need to show in a txtbox how
many Yes there are, and No, and NA. The 5 responses are
in combo boxes.
How do I go about counting these?
Thanks for any help.
*** John
 
John,

This would have been easier if you had designed your tables in a more
normalised structure. Anyway, with your present setup, you could put an
expression like this in the Control Source of each of the 3 counting
textboxes...


=Abs(([Question1]="Yes")+([Question2]="Yes")+([Question3]="Yes")+([Question4]="Yes")+([Question5]="Yes"))
 
Steve:
I was reading this post and wondered how you would've created the tables. I
understand "normalization" of data, but I would probably have the set the
tables up the same way as John.

I hope you get to read this and are able to respond.

regards,
--
from
Dave the wave~~~~~

Steve Schapel said:
John,

This would have been easier if you had designed your tables in a more
normalised structure. Anyway, with your present setup, you could put an
expression like this in the Control Source of each of the 3 counting
textboxes...


=Abs(([Question1]="Yes")+([Question2]="Yes")+([Question3]="Yes")+([Question4]="Yes")+([Question5]="Yes"))

--
Steve Schapel, Microsoft Access MVP

I have a form that has multiple questions on it. The first 5 are all
that need to be counted. In these first 5 the response is either Yes, No,
or NA. These 5 questions are part of one record. I need to show in a
txtbox how many Yes there are, and No, and NA. The 5 responses are in
combo boxes.
How do I go about counting these?
Thanks for any help.
*** John
Click to expand...
Click to expand...
 
Dave,

John stated "these 5 questions are part of one record". This is not
normalisation of data. Normalisation would mean having this data in one
field in 5 records. I do not know the details of John's project, but
here is an example of such a schema...

Table: Survey
SurveyID
Survey Date
etc.

Table: Questions
QuestionID
QuestionText

Table: Answers
AnswerID
SurveyID
QuestionID
Answer

This way, all you need is a simple Totals Query on the Answers table, to
count the number of Yes, No, and NA responses (in the Answer field) for
each relevant question (as per the QuestionID field).
 
Back
Top