By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,406 Members | 2,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,406 IT Pros & Developers. It's quick & easy.

Counting multiple checkboxes in a record

P: 1
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.
Dec 22 '13 #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Dec 22 '13 #2

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Archery: Abs(Sum([Contacts].[Archery]))
  2. Rifle: Abs(Sum([Contacts].[Rifle]))
  3. Fishing: Abs(Sum([Contacts].[Fishing]))
  4.  
The SQL Statement for these three Fields only would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Abs(Sum([Contacts].[Archery])) AS Archery, Abs(Sum(Contacts.Rifle)) AS Rifle, 
  2. Abs(Sum(Contacts.Fishing)) AS Fishing
  3. FROM Contacts;
  4.  
A Graph should easily be generated from this Query.
Dec 23 '13 #3

zmbd
Expert Mod 5K+
P: 5,397
ADezii,
Here I am making it too difficult (lol)

And for the indiviual records:
Expand|Select|Wrap|Line Numbers
  1. SELECT contacts.contacts_pk
  2.    , Abs([archery]+[Rifle]+[fishing]) AS zsum
  3. FROM contacts;
I still think the DB isn't normalized.
Dec 23 '13 #4

ADezii
Expert 5K+
P: 8,638
I'm sure that I am going to get negative feedback on this one, but Normalization isn't always desirable or practical.
Dec 23 '13 #5

zmbd
Expert Mod 5K+
P: 5,397
@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.
Dec 23 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.