An expression for counting Yes's as a defult in a tbl

  • Thread starter Thread starter Jen27
  • Start date Start date
J

Jen27

HI There,
I'd like to have a defult field in a table to display the number of "Yes's"
in the other fields of the table. Is that actually possible? or do I have to
do that with a query?

I'm assuming it's something along the lines of count([field name}if "Yes"...


Thanks,
Jen
 
You have to do this in a query. The expression would be something like:
Abs([fld1]+[fld2]+[fld3]+...)
Adding values across fields rather than across records generally suggests an
un-normalized table structure.
 
HI There,
I'd like to have a defult field in a table to display the number of "Yes's"
in the other fields of the table. Is that actually possible? or do I have to
do that with a query?

I'm assuming it's something along the lines of count([field name}if "Yes"...
Click to expand...

This type of derived data cannot and should not be stored in a table.
Use a Query instead.

The Count() operator counts RECORDS in a table, not values in a field.
You can use the fact that Yes is stored as -1, No as 0; in a Query you
can put a calculated field

HowManyYes: Abs([FieldA]+[FieldB]+[FieldC]+[FieldD]+ ... )

Note that your table structure is almost certainly incorrectly
normalized; you're storing data (the questions to which there are
yes/no answers) in fieldnames. What happens if you need to add a
question, or delete a question? Do you redesign your table, all your
queries, all your forms, all your reports? MUCH better is to have two
tables in a one to many relationship, with one question *per row* in
the related table.

John W. Vinson[MVP]
 
Back
Top