count "yes" answers for current record

R

robertm600635

I would like to build a query that would count all the "yes" and "no" answers
from 16 questions that are on the form and then calculate the totals for the
current record only and display those answers in a text box on the form. Each
of the 16 questions has a combox with the options "yes" and "no". Seems like
it should be easy but I can't figure it out.
 
D

Duane Hookom

Could you share your table structure? "calculate the totals" suggests
multiple records while "current record only" suggests just one record. You
can't have it both ways :-(

To count yes values in a yes/no data type field, you generally use an
expression like:
Sum(Abs([YesNoField]))
 
R

robertm600635

In the table there are 16 fields, one for each of the questions. In each
record in the table all questions are answered with a yes/no. I want to
calculate the number of yes's and no's for each record and have that total
displayed on the form. So if one client(record) answers 10 yes and 6 no I
want the 10 and 6 displayed on the form when that client's record is being
displayed. Hope I explained it ok. Thanks

Duane Hookom said:
Could you share your table structure? "calculate the totals" suggests
multiple records while "current record only" suggests just one record. You
can't have it both ways :-(

To count yes values in a yes/no data type field, you generally use an
expression like:
Sum(Abs([YesNoField]))
--
Duane Hookom
Microsoft Access MVP


robertm600635 said:
I would like to build a query that would count all the "yes" and "no" answers
from 16 questions that are on the form and then calculate the totals for the
current record only and display those answers in a text box on the form. Each
of the 16 questions has a combox with the options "yes" and "no". Seems like
it should be easy but I can't figure it out.
Click to expand...
Click to expand...
 
R

robertm600635

In the table there are 16 fields, one for each of the questions. In each
record in the table all questions are answered with a yes/no. I want to
calculate the number of yes's and no's for each record and have that total
displayed on the form. So if one client(record) answers 10 yes and 6 no I
want the 10 and 6 displayed on the form when that client's record is being
displayed. Hope I explained it ok. Thanks

Duane Hookom said:
Could you share your table structure? "calculate the totals" suggests
multiple records while "current record only" suggests just one record. You
can't have it both ways :-(

To count yes values in a yes/no data type field, you generally use an
expression like:
Sum(Abs([YesNoField]))
--
Duane Hookom
Microsoft Access MVP


robertm600635 said:
I would like to build a query that would count all the "yes" and "no" answers
from 16 questions that are on the form and then calculate the totals for the
current record only and display those answers in a text box on the form. Each
of the 16 questions has a combox with the options "yes" and "no". Seems like
it should be easy but I can't figure it out.
Click to expand...
Click to expand...
 
D

Duane Hookom

I'm not used to aggregating across fields. This usually suggests an
un-normalized database/table structure. You still didn't tell us if these are
text or yes/no fields.

Assuming they are Yes/No, you can simply add all the fields together and
calc the absolute value to count yes values.
TotalYes:Abs(fld1+fld2+fld3+fld4....)
--
Duane Hookom
Microsoft Access MVP


robertm600635 said:
In the table there are 16 fields, one for each of the questions. In each
record in the table all questions are answered with a yes/no. I want to
calculate the number of yes's and no's for each record and have that total
displayed on the form. So if one client(record) answers 10 yes and 6 no I
want the 10 and 6 displayed on the form when that client's record is being
displayed. Hope I explained it ok. Thanks

Duane Hookom said:
Could you share your table structure? "calculate the totals" suggests
multiple records while "current record only" suggests just one record. You
can't have it both ways :-(

To count yes values in a yes/no data type field, you generally use an
expression like:
Sum(Abs([YesNoField]))
--
Duane Hookom
Microsoft Access MVP


robertm600635 said:
I would like to build a query that would count all the "yes" and "no" answers
from 16 questions that are on the form and then calculate the totals for the
current record only and display those answers in a text box on the form. Each
of the 16 questions has a combox with the options "yes" and "no". Seems like
it should be easy but I can't figure it out.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

In the table there are 16 fields, one for each of the questions.
Click to expand...

Then your table design IS WRONG.

What will you do when you need to add a new question, or delete a question?
Redesign your table, redesign all your queries, rebuild all your forms,
restructure all your reports?

Normalize!! If you have a One (questionnaire) to Many (answers) relationship,
model it *as a one to many relationship* with one answer per RECORD, not one
answer per field.
In each
record in the table all questions are answered with a yes/no. I want to
calculate the number of yes's and no's for each record and have that total
displayed on the form. So if one client(record) answers 10 yes and 6 no I
want the 10 and 6 displayed on the form when that client's record is being
displayed. Hope I explained it ok. Thanks
Click to expand...

YesAnswers: -([A1] + [A2] + [A3] + ... + [A16])
NoAnswers: 16 + ([A1] + [A2] + [A3] + ... + [A16])


John W. Vinson [MVP]
 
D

Duane Hookom

I agree with John on the normalization issue. I should have pointed you to
this sample of how you might have organized your tables
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
Microsoft Access MVP


John W. Vinson said:
In the table there are 16 fields, one for each of the questions.
Click to expand...

Then your table design IS WRONG.

What will you do when you need to add a new question, or delete a question?
Redesign your table, redesign all your queries, rebuild all your forms,
restructure all your reports?

Normalize!! If you have a One (questionnaire) to Many (answers) relationship,
model it *as a one to many relationship* with one answer per RECORD, not one
answer per field.
In each
record in the table all questions are answered with a yes/no. I want to
calculate the number of yes's and no's for each record and have that total
displayed on the form. So if one client(record) answers 10 yes and 6 no I
want the 10 and 6 displayed on the form when that client's record is being
displayed. Hope I explained it ok. Thanks
Click to expand...

YesAnswers: -([A1] + [A2] + [A3] + ... + [A16])
NoAnswers: 16 + ([A1] + [A2] + [A3] + ... + [A16])


John W. Vinson [MVP]
Click to expand...
 
Top