count "yes" answers for current record

[Guest]

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.

 

[Guest]

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]))

 

[Guest]

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

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

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.

 

[Guest]

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

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

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.

 

[Guest]

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

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

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

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.

 

[John W. Vinson]

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

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

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


John W. Vinson [MVP]

 

[Guest]

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

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

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

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


John W. Vinson [MVP]