Multiple answers for a single field

[MrZimma]

Hi, this is my first post here.

My question is this: I am creating a database for a student survey. Some of the questions may have multiple answers.
For instance, question 2 reads:
"What location/time are you taking courses (select all that apply)"

and the possible answers are:
1)Daytime/Oakland
2)Evening/Oakland
3)Saturday College
4)BC

- Where a student could be taking classes at multiple locations or times. I am completely lost as to how to set this up. I have tried to set the properties for the field on the form to accept multiple responses, but of course, these do not save to the table and so it's completely useless.

Thank you in advance for your help!!!

 

[Pat Hartman]

Multiple answers should not be stored in a single field. You will have lots of trouble trying to do any statisitics if you do this. You have a 1-many relationship and the many-side of a relationship needs to be stored in a separate table. That will allow as many choices as necessary and keep the data separated so you can report on it.

Do some reading on normalization (or normalisation as it is spelled here).

 

[MrZimma]

OK, I read up on it, and I don't think I'm finding what I need. It's telling me I need to analyze the table? I'm more than slightly confused. Let me tell you how the db is set up.

I have 2 tables, one is the "master" table called tblSurvey. (in this table is a field called Location)

The other table is called tblLocation. In this table are the locations classes are offered.


My goal is to create a form on which the user can select more than one location, as many of our students take classes at several locations.

Am I on the right track? Access help is extremely... unhelpful. It gave me information on the Analyzer Wizard and the Append Query when I looked up Normalization.

Do I want to set up a relationship of 1(Location field in tblSurvey) to many (ALL fields in tblLocation)? And where do I go from there?

I used to know how to do this stuff but it's been about 5 years since I've had to use any of it, so I suppose I've 'lost it'.

 

[Pat Hartman]

Will the student answer each question for each location or are the questions unrelated to location?

 

[MrZimma]

The locations are the possible answers to the question.

It's like this:

Q = At what location(s) are you taking courses? (please select all that apply)

A = Oakland
A = Saturday College
A = Mt Lebo
A = Monroeville

 

[The_Doc_Man]

Your problem is a many-to-one relationship for some of your answers.

This is a case where a careful knowledge of the FUTURE of your DB becomes important. The scrupulously correct method is overkill if your DB is ad-hoc and will never grow in complexity of questions. But if it is intended as a general tool that will grow over time, you've got a normalization problem.

More than one location can be a correct answer to your question ("pick all that apply"), so you have a many-to-one relationship between locations and questions asking about locations. This means that the question and its answers have to be in separate tables joined by a common question number or some other method of associating the questions and answers.

This leads to potentially a very complex solution. Before we can advise you properly (between the "very quick and very dirty" choice or the "technically correct but somewhat cumbersome" choice), we need to know if this project has a future. This is one of those rare cases where I could condone (and advise) a non-normalized database IF your DB is going to be discarded and never used again, never used as a springboard for further designs, and never going to be submitted as a class project. If it has ANY FUTURE USE AT ALL, then we cannot take the quick-and-dirty path because you will need the design to contain no impediments to your future surveys.

 

[MrZimma]

ahhh that's what I figured. I ended up taking the easy route and just assigning different fields for the different answers, and setting up the form accordingly. Thank you all for your help though.