Counting multiple checkboxes in a record

[maconstable]

Greetings. I administer a membership database for a rod and gun club I belong to. In the contacts table I have several fields including member name, member id number, etc. In the same record there are check boxes for what they are interested in. i.e. Archery, Rifle, Fishing, etc.
What I'm looking to do is get a count of each of the interests. The outcome should be Count of Archery is 123, count of rifle is 321, count of fishing is 222.
I'm thinking a query may be best. That way I can take the data and generate a graph.

Thanks in advance, any help is appreciated.

Version is Access 2013. I'm not afraid to use vba either if that is the best way.

[zmbd]

maconstable:
Your database isn't normalized and this results in making what you want to much more difficult : > Database Normalization and Table Structures.

There is a way to do what you want for the counts; however, it's a very tedious and overly complex method. It involves a series of "iif()" "NZ()" and a "Sum()" in a caculated field. Fine if you'll never ever ever add another interest but a real pain to maintain AND you'll have to have this series of calcs for each interest that your after and so forth...

Here's what I advise:
1) read the link I provided above. If you get lost don't worry, ask here and we'll get you thru it.

2) One table for the customers/clients

3) One table that lists interest

4) A table that links the customers to the the interests.

5) in the table relationships tool we establish the relationshops between these three tables.

6) We build the queries based on the table in (4) pulling by customer and/or interest as needed.

Sorry I'm on my way out the door or I'd provide some more detail and I'm sure Rabbit or Neopa will be along shortly.

[ADezii]

This is a relatively simple matter if you are looking for the Totals only. The solution involves a Totals Query consisting of the actual Field Name itself as a Calculated Field, and Expression in the Total Row. Each Column of the Totals Query would look like:

Archery: Abs(Sum([Contacts].[Archery]))
Rifle: Abs(Sum([Contacts].[Rifle]))
Fishing: Abs(Sum([Contacts].[Fishing]))

The SQL Statement for these three Fields only would be:

SELECT Abs(Sum([Contacts].[Archery])) AS Archery, Abs(Sum(Contacts.Rifle)) AS Rifle, 
Abs(Sum(Contacts.Fishing)) AS Fishing
FROM Contacts;

A Graph should easily be generated from this Query.

[zmbd]

ADezii,
Here I am making it too difficult (lol)

And for the indiviual records:

SELECT contacts.contacts_pk
   , Abs([archery]+[Rifle]+[fishing]) AS zsum
FROM contacts;

I still think the DB isn't normalized.

[ADezii]

I'm sure that I am going to get negative feedback on this one, but Normalization isn't always desirable or practical.

[zmbd]

@A: Not from me... (^_^) - I have a few databases where I've had to flatten the data, usually for a required dataentry form. For some reason, no-one thought a CTQ style select query would be needed, sigh.