How to create form or survey with same multiple choice answers

Question

I have a need to create a SharePoint questionnaire for building inspections. These inspections are to be performed weekly by different departments Each area to be inspected will have a reply of: satisfactory, unsatisfactory, safety hazard, or well maintained. The questions will be grouped according to departments. Once all answers are submitted, I need to be able to total the number or satisfactory responses, US responses, etc.
I've tried using the out of the box survey with a lookup to the answer choices, but the export to excel does not give me a good way to total the responses.

Example: Kitchen Questions Q: Floor Answer choices: S, US, SH, WM Q: Refrigerator Answer choices: S, US, SH, WM

Warehouse Questions: Q: Forklift Answer choices: S, US, SH, WM Q: Storage Shelves Answer choices: S, US, SH, WM

Once these questions are answered, I will need to total the number of each answer choice according to the departments.

I've also tried using InfoPath with yes/no checkboxes by each answer choice. Needless to say, this created way too many fields to keep track of.

Any and all ideas are appreciated!

Answer 1

I've made something similar once, with around 95 questions that was answered by around 400 people.

My weapon of choice in these situations is InfoPath and promoting the anwser fields to fields in a SharePoint list. You should only end up with one SharePoint field per answer.

Use the choice field in InfoPath to create either lookup choices from SP-lists or type them in manually.

When you need to run analytics on your answers, turn to Excel. Pull all the data from the list into Excel and do your counting and graphs there.

Naming your fields and organizing them in InfoPath is key to keeping everything structured.

Hope it helps.

Answer 2

This is not possible out of the box. You will need code or 3rd party tools.

InfoPath would be a good track to follow. If you want to work with surveys a lot, you may want to have a look at the techniques and tools offered by Qdabra Software. They have a few inexpensive (as in: a licence costs less than a day's developer time) tools to help with these scenarios. Qdabra also have many video tutorials on YouTube to help get familiar with their technology.

I am not connected to Qdabra, I am just a very happy user of their tools, which are a chest of nuggets for people like me who don't use code.