More Questionnaire Advice Pls?

[heathers]

Hi; have searched and read forums for Questionnaire/Survey design, but am still confused!

I have one survey (which will not be repeated, and there will never be more than one survey). I am the only person who will input the data (from either web input form converted to csv or Excel, or from returned paper surveys). I have not inputted any data, as I want to get the design correct/normalized etc before doing so.

I have set up tables according to info on these forums: ie separate tables with PK/FK etc for Respondents/Questions/etc etc. However, I am still confused about the Answers (Response Options) table; many questions are multiple choice, eg. Yes/No; Strongly Agree to Strongly Disagree etc etc. But I don't know how to put this in a table, because although some questions have answers unique to them, some have answers in common with other questions (ie Yes/No). I also want to capture no reponses.

Do I list each and every possible choice, somehow linking it to the question; eg Q1a 1 Yes, Q1b 2 No; OR do I make a different column for each type of response, and then use lookup tables to reference, say an "Opinion" field, or an "Age Range" field? And how do I account for people not responding, as I need to do this for EACH question?

Thanks so much for any help.

 

[DCrake]

Hear is my opinion.

Firstly you have 1 questionairre which is filled in by many people. So you need one table to hold any data about who filled in the survey, if needed, and one tabel to collect their responses.

Each question can have a different type of response, be it a straight forward yes/no or a multiple choice Excelent to Poor, Multiple choice, or even comments.

So if question 1 is: What is your Gender M or F You would need a text field with 1 character that accepts (M)ale, (F)emale or (U)nknown

If question 2 was Wat age group do you fall into 18 or under, 18 -25, 26, 40, etc. Again you only need one field to capture the data.

However if Q3 was "How did you hear about us:" Internet, Newspaper, Recommended, TV, etc) tick as many as you want." This becomes a bit harder to collect. What you need to do in this case it to create another table that has a PK for the record, a FK that is the PK of the survey record and a cod eto capture the reponses from the user. This means you can have one or more answers for Q3.

Now lets say Q3 has the option "Other (please state)" you can have a field in your survey table called Q3Other which can hold that info. Now when you come to do your analysis you would create a union query on your Q3 table and your Q3Other field and by grouping on them would get an accurate result.

Just think about the type of response that will be recorded and how you would go about recording it.

Hope this helps you a bit.

David

 

[heathers]

Hi David

"...you need one table to hold any data about who filled in the survey, if needed, and one tabel to collect their responses."

Got that. And the following:

"...Each question can have a different type of response, be it a straight forward yes/no or a multiple choice Excelent to Poor, Multiple choice, or even comments"

My question relates more to how I would input the data in the answer table, and how this is linked to my main Form for input:
- do i do a strict normalized vertical structure for the WHOLE DB, and have the table (copied from another post in this forum)

"tblResponseOptions
ResponseOptionID (PK)
ResponseText
ResponseValue"

...and in which case, what does the data inputted actually look like? would it be something like the line below...and be repeated for EACH question? (and I don't know how I would input the data for different questions that can have the same answer - eg how do I link the PK to tblQuestions?)

ID: Q2aii Text: "male","female","Missing" Value: 1,2,99?

OR can I do most of the DB in a nice normalized vertical manner (ie Questions, Respondents etc) and have the Answers table in a different format; such as the following, taken from this link: http://www.researchtutorials.com/data-management/create-a-survey-database-in-access-part-i/

"tblResponseOptions
Filter(QuestionID or Code)
ResponseValue
ResponseText"

which would look like this (with each option in a different row):

Row 1 - Filter: Q1a (SEX); ResponseValue: 1; ResponseText: Male
Row 2 - Q1a; 2; Female
....etc

and I'm not sure how I would edit any relationships, because I only want to use the values in lookup tables in my input form to quickly input my data?

...re your other stuff; all my questions are limited to only one response per question (making things easy for myself) and none have "other" fields. I have one further comments question, but will make a separate table for that one (as you mention).

cheers and thanks!
Heather

 

[DCrake]

Lets look at this though an example. The example I am going to use is:

"How do you rate this answer"

Options allowed are Excellent, Very good, Average, Very Poor & Crap

Now we could say that we only need to store the first letter of the response in the table and eveluate it later, however 2 of the responses share the same letter.

So what we are going to do is to create a table called TblResponses. In that table will be 2 fields

Field 1: fldID - Autonumber - Primary Key
Field 2: fldResponse - Text 12

We are going to add the 5 options to the table.

In our main survey table we will have a field named fldQ1. This is where we will store the response.

On our data collection bound form we are going to place a combo box

Combobox: CboReponse
Rowsource Type : Table/Query
Rowsource: TblResponses
Columns: 2
Column Widths: 0cm;3cm
Bound Column:1 - (fldID)

This combo box is bound to the field fldQ1 in our underlying table.

So when we pick a response from the combo box the bound column will be stored in the main survey table fldQ1 and act as a foreign key (FK).

When we come to analyse the data we can then create a query and create a join between the fldQ1 in the main table and fldID in the tblResponses and bring down the fldResponse in the query so we see the decription as opposed to the FK

Again we could set this configuration up in our relationships:

Tblsurvey - fldQ1 <-> TblReponses - fldID

In the situations where the answers are simply Yes/No or Male/Female there is no need to create the above simply change the rowsource type of the combo box to Value list and in the value list add your options

"Male";"Female";"Unknown"

or

"Yes","No"

The only thing you need to ensure is that the field type in the survey table matches the type of response the user will select.

Hope this clarifies it a bit more for you.

David

 

[DCrake]

Example 2

In the case where the user can select more than one answer to a question

You will need to create a further table

tblQ3

This will have 3 fields

fldID: Autonumber - Primary key
fldRecID: Long Integer - Primary key of the record in the main table
fldResponse: Long Integer - Primary key of the record in the tblReponses table


So if the interviewee as ticked boxes 1,2 & 4

the table would look something like

fldId : 1
fldRecID : 1
fldReponse : 1

fldId : 2
fldRecID : 1
fldReponse : 2

fldId : 3
fldRecID : 1
fldReponse : 4


In our relationships we would have a join between the fldRecID in the tblQ3 tabel to the fldID in the main survey table

There would also be a join between the fldResponse in the tblQ3 table and the fldID in the tblResponses table.

We would treat the query in much the same way as we did in the earlier example.

David

 

[foxtrot123]

I realize this post is a bit old so I'm not sure if anyone is still following it. But just in case ...

DCrake:

I'm curious why you didn't recommend a normalized structure for heather's survey database.

For example:

So if question 1 is: What is your Gender M or F You would need a text field with 1 character that accepts (M)ale, (F)emale or (U)nknown

A normalized approach to this and similar questions would involve the standard four-table solution:

tblRespondent
(people who completed the survey)

tblQuestions
(questions on the survey)

tblAnswerChoices
(answers choices for each question)

tblResponses
(actual responses by the respondents)

Thoughts?

 

[vbaInet]

Spam reported - xuelanghu521