Adding up tick boxes in Access

Question

I have an Access database with a list of questions with yes/no tickboxes.  They are feedback from visitors to our organisation. I want to add up the tick boxes so that we just have the totals and then make a chart.  I can probably do the chart, but don't know how to add up the ticks.  Can anybody help?

Answer 1

Jeanette's and Ken's answers are correct if your database was properly normalized, but I'm afraid it isn't. In a properly normalized database each question would be a record in a table. I'm afraid, what you have is a bunch of Yes/No fields in a table, each field representing a question. This will make analyzing the results of the questionnaire more difficult in the future. Also makes maintenance of the questionnaire more difficult. So I would urge you to think about redesigning your database in a more normalized fashion. 

You will find an example of a properly designed questionnaire database here:

http://answers.microsoft.com/en-us/office/forum/office_2007-access/can-i-create-rules-for-fields-can-a-certain-answer/443f3327-e544-41a6-8fc8-07240fa5a309

But to answer your question, just add the field. In a query add a column:

TotalYes: ABS(field1+field2+field3...)

As noted a Yes is stored as a -1 so the ABS() function turned it in a unsigned number)

Answer 2

But whose answer addresses the right question?  I'm not sure from your original post.  The requirement could be for summation (Jeanette's solution expanded by me) or addition (Scott's solution).

Do you want to sum the 'yes' answers to each question over multiple rows, i.e. how many people answered 'yes' to each of the questions?  That's summation and Jeanette's/my solution is the answer.  Or do you want to sum all the 'yes' answers in each row in the table, i.e. to how many questions did each person answer 'yes'?  That's addition and Scott's solution is the answer.

Scott is quite right about the table design, however.  Having multiple columns, one for each question, is what is known as 'encoding data as column headings'.  Strictly speaking it is not a normalization issue, though, it's more fundamental than that.  It is really a violation of Codd's Rule #1, the Information Rule which, in informal language, requires that all data be stored as values at column positions in rows in tables, and in no other way.  It was Edgar F Codd who introduced the database relational model back in 1971 ( http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf ), and laid the theoretical foundations for all relational database management systems.  The process of normalization ( http://en.wikipedia.org/wiki/Database_normalization ), which minimizes redundancy by the decomposition of a table into a set of related tables, can't really be applied until this fundamental rule is adhered to.

When you have multiple columns, each representing a different data value (a question in this case), what you in essence have is a spreadsheet pretending to be a database table.  It can be used to store the data, and to some extent to process it, but you will soon run up against its limitations.

Answer 3

When you switch the above query to datasheet view, you will see that the total is a negative number.

Access uses -1 for a check box marked True.

Which you can of course reverse with:

SUM(ABS(Test.Friendly))

or

SUM(Test.Friendly*-1)

or (my preference as they avoid being unduly chummy with the implementation)

SUM(IIF(Test.Friendly,1,0))

or

COUNT(IIF(Test.Friendly,1,NULL))

Answer 4

Thank you so much - this looks like just what I need.  Only problem is I'm still not sure how to do it, as I am not so expert on Access.  Is there any possibility at all that you could write this step by step? Sorry to be a pain....

Answer 5

Here is a query that sums the yeses in a table called tblTest.

There is a field called Friendly - Yes/No field.

To get the same results as me, create tblTest with a field called Friendly. Add some rows by ticking the check box or leaving it unticked.

To find how many ticks there are for Friendly,

SELECT Sum(tblTest.Friendly) AS SumOfFriendly

FROM tblTest;

When you switch the above query to datasheet view, you will see that the total is a negative number.

Access uses -1 for a check box marked True.

In a similar way you can create a separate query to count the ticks in other yes/no fields.

If you want all the fields in the same query, do it like this - I added an extra field called Helpful.

SELECT Sum(tblTest.Friendly) AS SumOfFriendly, Sum(tblTest.Helpful) AS SumOfHelpful

FROM tblTest;

To create your own query, in the query design change the query into a Totals query (Right click on the query grid and choose Totals. In the row called Total: choose Sum.)