Find count of drop down box selections in a specific record

[Chris Trent]

Hi Everyone

Please could you assist me. I am not very advanced when it comes to MS Access so i would appreciate a step by step guide if possible.

I have a database to track different kinds of cleaning carried out. The table consists of quite a few columns as they are all questions that need to be answered. The question answers come from combo boxes. The options are Yes, Partially or No.

I would like to create a query to be used in a report that counts the number of questions that had an answer "Yes" so that I can work out the percentage the task was completed. For example, if the form as 10 questions in total and 6 were answered with "Yes", then I know it was 60% complete. I'm not experienced in SQL but I have tried building various queries in the Query Design screen and in SQL examples but I cannot find what I am looking for.

I would like to pull the number of "Yes" answers per record, not the total number of records containing "Yes". It would be great if i could store the number of Yes per record in the table. I am using Access 2010 64-bit.

I'm so frustrated and would appreciate any help.

 

[Isaac]

I'm not sure how many columns you have, thus, I'm not sure exactly how tedious the solution I'm attaching might be for you. (Then again, it's only tedious one time--during query design, after that, you're set and adding additional columns would be minimal work).

Here is a simple, not fancy way of doing it - See attached. There are other ways too, I'm sure. One would be if you changed your current column type and design it to hold only numbers like 0, 1, or Null, which special meanings for each, and then performed some math on them...Or better yet, a checkbox field with triple state allowed (although I recommend never using creative data types, like checkboxes). But I figured I'd provide the simple thing that first came to mind.

 

[Chris Trent]

Thank you so much for your reply Isaac.

I wasnt sure exactly how the IIF function works. Thank you for this.

What does the "/5" do. Should i change this to adjust to the number of columns?

 

[Chris Trent]

I just figured the above out with the /5.

Thank you once again for your help.

 

[Isaac]

Yep - perfect! Glad it helped.

 

[Pat Hartman]

You might consider normalizing the table schema. ALL those questions belong a one per row in a separate table rather than 100 (or whatever) columns in one table. This is a spreadsheet design which is only a problem because Access is a relational database and not a spreadsheet. That means that there are NO functions that operate "across" a number of columns. Functions only operate down rows in a table of a relational database.

The function that Isaac built for you will probably solve your problem but this is the first of many problems you will have with this design. If you are still early in the design phase (and even if you are not), fixing the schema will save a lot of aggravation and extra work in the future.