Help with tallying checkboxes

Question

I have a Table listing names of people with Yes/No check boxes of events they would like to participate in. There are 1200 names and 21 categories of yes/no fields. I have made a query of each category to get the checkmark totals, I just wrote "yes" in the column in design view but why cant all the categories be in one place showing me all the totals. (21 checkboxes in all so I have 21 queries for each catagory, I would like one query). I tried and cannot figure how on my own. Here's a sample:

SELECT [2016 SR17 Registrants].[Attend General Sessions], [2016 SR17 Registrants].[Attend Registration Breakfast], [2016 SR17 Registrants].[Participate in Veteran Color Guard], [2016 SR17 Registrants].[Participate in Parade]
FROM [2016 SR17 Registrants];

Answer 1

Hi. I think the short answer to your problem is to use a UNION query, but the real answer is to redesign your table structure to use a proper parent-child relationship rather than a series of repeating yes/no groups. Just my 2 cents...

Answer 2

You can conditionally count a column by summing the return value of an expression which returns 0 or 1:

SELECT
SUM(IIF([Attend General Sessions],1,0)) AS [General Sessions Attendance],
SUM(IIF([Attend Registration Breakfast],1,0)) AS [Registration Breakfast Attendance],
SUM(IIF([Participate in Veteran Color Guard],1,0)) AS [Veteran Color Guard Participation],
SUM(IIF([Participate in Parade],1,0)) AS [Parade Participation]
FROM [2016 SR17 Registrants];

However, as theDBguy points out, the reason you need to jump through these hoops is the bad design of your table.  By having a separate Boolaen column for each category you are 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

What you have is a many-to-many relationship type between registrants and event categories.  This is modelled by a third table which resolves the many-to-many relationship type into two one-to-many relationship types.  So the model for the database would include, in broad outline, three tables as follows:

Registrants
….RegistrantID  (PK)
….FirstName
….LastName

EventCategories
….EventCategoryID  (PK)
….EventCategory

and to model the relationship type:

EventRegistrations
….RegistrantID  (FK)
….EventCategoryID  (FK)

The primary key of the last table is a composite one made up of the two foreign key columns.

You'll find an example of this basic type of many-to-many relationship type as StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates a number of possible interfaces for this type of relationship type, but I would strongly recommend the use of a conventional form/subform.  This is very easy to implement and, unlike the others interfaces, allow you to include non-key attributes of the relationship type, the Status attribute in my demo being an example.

In the same OneDrive folder the UnencodeColumns demo illustrates how you can automatically recast the data from an incorrectly structured table into a set of correctly designed related tables.

---

Ken Sheridan, Stafford, England

Answer 3

Thank you very much for your response. I'm very grateful you gave instructions and a link. It's very helpful. I will try!