Using fields containing a checkbox

[Guest]

I have an extension database with students records. We surveyed the students
as to whether or not they had 40 individual assets. Each asset has its own
column and a checkbox that we checked if they had that asset. Now I need to
find the number of assets each student has. Is there a way to add the number
of boxes that are checked "yes"?

 

[fredg]

I have an extension database with students records. We surveyed the students
as to whether or not they had 40 individual assets. Each asset has its own
column and a checkbox that we checked if they had that asset. Now I need to
find the number of assets each student has. Is there a way to add the number
of boxes that are checked "yes"?

To find out how many were checked in the same field:
=Abs(Sum([CheckField]))

To find the total number of checked over several check box fields:
=Abs(Sum([CheckField1]) + [CheckField2] + [CheckField3] + etc))

 

[Jeff Boyce]

What was implied in Fred's answer is the apparent need for more
normalization of your data structure. It sounds like you've committed
spreadsheet on Access, adding yet another column when you have yet another
asset.

This approach requires you to modify your table, your queries, your forms,
your reports, your code, etc. every time the number of assets change. This
also make both you and Access work a lot harder, as Access is NOT a
spreadsheet.

Fred's first response points to a more normalized design, in which you have
a table of students, a table of assets, and a table of student-assets (one
row per each asset associated with a student). The number of rows in this
third table (per student) is the number of assets that student has.

And when you add three new assets you are checking for, add them to the
assets table and use a form you to associate a student with any/all assets.

 

[John Spencer]

If, for some reason, you are stuck with the current table design then you
can do

Field: Abs(FieldA + FieldB + Fieldc + FieldD + ... + Field40)

This assumes that your fields are Yes/No fields.
Also, if your field names are long, then you will run into the size limit
for entering data into the query grid "cell".

You're better off if you can redesign your table structure.

The other option would be to write a VBA function that would do this.