This is not a big challenge, it’s more of just trying to decide which is the best way to do it. I need to input data from a questionnaire. We have 200 hundred or so clients and each client has a several hundred customers. The customers are asked to fill out a 10 question questionnaire.
Each question has 5 answers, which will be given a value from 1 to 5, with one being the worst and 5 being the best. I don’t recall the answers exactly, but for the first 5 of the questions the answers are something like….
Very Poor
Poor
Ok
Good
Very Good
For the last 5 questions the answers are something like…
Never
Not Often
Seldom
Regularly
All The Time
This is going to be an annual thing, and the questions might change, so I’m trying to make the code and tables as transparent and reusable as possible. I set up a table for the answers like this.
ClientID – Integer
AnswerSequence – Integer
Answer – Integer
The AnswerSequence will be 1 through 10 to represent which question, and then the Answer will be 1 through 5 to represent the answer.
I have a look-up table with the text version of the answers. There are 2 fields, with one being Integer and one being text. The integer values for the answers are used to build queries to populate listboxes with the answers for data input. It looks like this
1 - Very Poor
2 - Poor
3 - Ok
4 - Good
5 - Very Good
6 - Never
7 - Not Often
8 - Seldom
9 - Regularly
10 - All The Time
The problem is, if question number 8 has an answer of 2 it should equate to “Not Often” because it’s in the second half of the questionnaire. However, because the value is 2 the answer will come back as “Poor” from the look-up table.
If I add to the value as the data is input I could add 5 to the value of questions 6 through 10 and then I could retrieve the correct answer from the look-up table. However, that could throw off the reporting because we may want to give average scores. If that’s the case I would need to subtract 5 from the answers for all questions 6 through 10.
These are simple solutions that seem inelegant. Another option is to add a forth field to the answers table that would join to the look-up table. The answer for question 8 would be 2 and then there would be a value of 7 in the 4th field to retrieve the text version of the answer. For that matter, I could just store both the value 2 for the answer and then have a text field as well.
Any other ideas?
Each question has 5 answers, which will be given a value from 1 to 5, with one being the worst and 5 being the best. I don’t recall the answers exactly, but for the first 5 of the questions the answers are something like….
Very Poor
Poor
Ok
Good
Very Good
For the last 5 questions the answers are something like…
Never
Not Often
Seldom
Regularly
All The Time
This is going to be an annual thing, and the questions might change, so I’m trying to make the code and tables as transparent and reusable as possible. I set up a table for the answers like this.
ClientID – Integer
AnswerSequence – Integer
Answer – Integer
The AnswerSequence will be 1 through 10 to represent which question, and then the Answer will be 1 through 5 to represent the answer.
I have a look-up table with the text version of the answers. There are 2 fields, with one being Integer and one being text. The integer values for the answers are used to build queries to populate listboxes with the answers for data input. It looks like this
1 - Very Poor
2 - Poor
3 - Ok
4 - Good
5 - Very Good
6 - Never
7 - Not Often
8 - Seldom
9 - Regularly
10 - All The Time
The problem is, if question number 8 has an answer of 2 it should equate to “Not Often” because it’s in the second half of the questionnaire. However, because the value is 2 the answer will come back as “Poor” from the look-up table.
If I add to the value as the data is input I could add 5 to the value of questions 6 through 10 and then I could retrieve the correct answer from the look-up table. However, that could throw off the reporting because we may want to give average scores. If that’s the case I would need to subtract 5 from the answers for all questions 6 through 10.
These are simple solutions that seem inelegant. Another option is to add a forth field to the answers table that would join to the look-up table. The answer for question 8 would be 2 and then there would be a value of 7 in the 4th field to retrieve the text version of the answer. For that matter, I could just store both the value 2 for the answer and then have a text field as well.
Any other ideas?