Access Database - multiple value issues

[Guest]

I am creating a database for questionnaire results. In a couple of questions
people are able to choose more than one answer e.g. Howles Road and Tamper
Way and any other from a list.

How do I store these and what relationships in tables do I need.

I have a table called Questionnaire Results which has Questions 1 - 50 and
various responses available.

I am struggling with multiple values in an answer and can't work out table
relationships to store the data. The example above is for them to state a
location they would like to see something placed in and they can have more
than one.

Sorry if I seem dim, it's a long time since I created a database and this is
voluntary!

Thanks for any help.

 

[Pat Hartman\(MVP\)]

You need a separate table to hold the answers when there can be more than
one of them. The simplest thing is probably to store the single and
multiple answers in the same table for consistency. So, you'll end up with
something like:
tblAnswers:
AnswerID
SurveyID (foreign key to tblSurvey)
RespondentID (foreign key to tblRespondent)
QuestionID (foreign key to tblQuestion
AnswerSeq (generate a sequence number for each answer)
Answer

You should have a unique index that includes SurveyID, RespondentID,
QuestionID, and AnswerSeq to prevent duplicates.