how to design database to handle survey data with multiple respons

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am in the process of designing an access file to sort and analyze data from
a survey. There is one survey question that has multiple answers (respondent
can check up to 25 selections). I was going to just create a query that
searches within each record using wild cards, but the formula was getting
long and tedious. It'll work. Cuz I've programmed this type of query before,
but is there other ways to handle this type of data? I mean, is there a
better way to treat cells that have multiple data points? Maybe Access has a
feature that can treat each selection as a sub-record when text is seperated
by commas or something like that? In addition, I'm not a fan of creating a
new field for each selection.

Thanks for any input you can give. Hope I didn't confuse any one.
 
Rather than create a field for each response, you could create a record for
each valid response. The tables would look something like this:

tblRespondant
RespID
RespondantName
Etc.

tblSurvey
SurveyID
SurveyName
Etc...

tblQuestions
QuestionID
Question

tblResponse
ResponseID
Response

trelSurvey_Question
SQID (Autonumber)
SurveyID
QuestionID

*SurveyID and QuestionID are a multi-part key

trelSurvey_Question_ValidResponse
SQVRID (Autonumber)
SQID
ResponseID

*SQID and ResponseID are a multi-part key

trelRespondant_Response
RespondantID
SQVRID

*RespondantID and SQVRID are a multi-part key


Something like this would allow you to assign questions to a survey, valid
responses for the questions, and then responses from the survey takers.
Multiple responses are allowed for a question, but in cases where only a
single answer is allowed, you'll need to do some validation.
 
Are you suggesting that "At Your Survey" doesn't allow the survey designer
to create multiple types of responses?
 
Thanks Paul, I'll try it out. The particular question in my survey that
sparked this inquiry is definitely not a yes/no question. ... I'm not very
proficient with SQL though.

Also, thanks duane, I'll check out that website.
 
It might...but it isn't an especially easy example to follow. And you did
suggest that each response would need to be treated as a yes/no.
 
My response was directed at "check up to 25 selections". Each of those 25
selections would be a yes or no type question. At Your Survey allows the
survey designer to create any number of "canned" selections for the response
combo box. AYS would not handle a single question with multiple selections
in its current state. For instance:
Select colors that you like: _Green, _White, _Blue, _Red, _Purple
This could be included in AYS as 5 yes/no questions.
 
Back
Top