how to design database to handle survey data with multiple respons

[Tom]

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.

 

[Paul Overway]

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.

 

[Duane Hookom]

Consider reviewing the "At Your Survey" demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Each selection would need to be treated like a separate question with a
yes/no type response.

 

[Paul Overway]

My suggestion would allow a variety of responses other than yes/no.

 

[Duane Hookom]

Are you suggesting that "At Your Survey" doesn't allow the survey designer
to create multiple types of responses?

 

[Tom]

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.

 

[Paul Overway]

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.

 

[Duane Hookom]

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.