Counting yes values (Access XP)

Topic

New Reply

I have a table with one autonumber field, one text field and 60 yes/no fields to track objectives complete. For each record I need a count of yes values and a count of no values. Any ideas on how to count yes values in a single record? I have thought on it all last week and could not come up with any ideas.

Carla

Reply | Quote

Replies

This is a very unfortunate table design. Instead of 60 Yes/No fields, you should have a separate table with an ID field (linked to the AutoNumber field in your table), an Objective ID field and one Yes/No field. You could then use a simple group by query to count the number of Yes values.
In the table as is, you’re forced to do something like:

CountYes: Abs([Field1]+[Field2]+[Field3]+…+[Field60])

CountNo: 60-[CountYes]

Reply | Quote

I agreeit is a poor table design, I am thinking a redesign might be the best solution. I thought I might look at any ideas to get the information I need, but you have just confirmed I might as well just get to fixing the real problem.

As always thank you,
Carla

Reply | Quote

Hans,

Just as a followup I spent the time to redesign the existing database and queried out all the old data into a new structure. The original structure was built long before my time and I am always reluctant to change what has already been done, even if I think the structure and design are poor. The result is even better than I would have thought and well worth the time spent. I can now display a screen with all the yes values and the record count gives a very easy to view answer of total yes values.

Carla

Reply | Quote