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.