How do I count mulitple check boxes in one query in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a database with 15 checkboxes for users to answer "yes" they have
completed something or "no" it is not yet done. I grouped this by my
company's affiliates. I need to run a query, sorting by affiliate, that
counts the number of "yes"es for each question. Then I need to run a query
that calculates the percentage of "yes"es for each question. This seemed
like a simple task at first, but now I can't seem to make it work. Can
someone please help? Thanks!
 
I would hope that you haven't created a spreadsheet in Access ... if your
table has 15 Yes/No fields, you have!

To "count" the number of "Yes" values, create a query, create a new field in
which you add all the Y/N fields, and take the absolute value of that sum.
Since "Yes" = -1 (or 1 in SQL-Server), the sum of the fields is the sum of
the "Yes"s.

Good luck

Jeff Boyce
<Access MVP>
 
Access is a relational database. If you have one-to-many checkboxes that
might apply, you have a relationship that you'd model in Access with a "one"
table (a "parent") and a "many" table (the "children" -- your Y/N info).

If you were using a spreadsheet, you couldn't do this, so you'd end up
adding more columns for each Y/N item.

In the Access (relational design) version, you'd only have as many children
rows as you had "Yes"s.

Check Access HELP on normalization and relational design.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top