Need help storing multiple answers per question

[Buakaw]

Hi,


I'm trying to modify a survey DB I downloaded from the web, so that it can accept multiple answers per question. The original will only accept one answer for each question.

Here's the link to the original:

http://rogersaccesslibrary.com/forum...4/AYS_2000.zip

I've attached a screenshot of the relationship between tables.

Description of tables:
tblSurveys : Keeps track of which survey this is, e.g. customer service survey, favourite ice-cream flavours survey, etc.

tblSrvRspns: Keeps track of the survey respondents, i.e. the people who took the surveys. E.g. John, Bill, Jane, etc.

tblQuestions : Keeps track of the questions in each survey.

tblResponsesList: Keeps the lists of predefined answers that users can choose from to answer the survey questions. E.g. question is which is your favourite colour, tblResponsesList will have entries for Red, Blue, Green, Yellow, etc.

tblResponses : Keeps the answers that the respondents enter for the questions.

I put a ListBox on a form to allow users to enter multiple answers per question. E.g. on what days do you watch TV? They can select more than one day.

I tried to use code in VBA to store the answers in tblResponses. I got this error message:

"The changes you requested were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
Is this because I am not allowed to make duplicate entries with the same RspnsID and QstnID in tblResponses?

Question:
How do I modify the tables to be able to handle multiple answers (tblResponses.Rspns) per question?

I thought about this for some time and still can't think of how to do this.

Thanks for any help you can give.

[rpeare]

Storing multiple values in a single field is an extremely bad practice. Please do not do it. Instead create a sub table that has the ability to store the multiple repsonses you need

For instance

instead of having RACE stored as White;Black;Asian

have a sub table that has the foreign key to your primary table, a unique identifier, and the raceID.

This will be more work in setting up but the problems in dealing with a multi value field are legion especially for newer users.

[Buakaw]

Thanks for the response.

However in the example I'm working on, the primary table (tblResponses) has 2 keys which identify the records uniquely, namely RspnsID (the person who did the survey) and QstnID (which question is it).

In this case how do I set up a separate table to be able to store multiple answers to each question? I can't figure that part out.

[rpeare]

does your tblResponses have an autonumber field? Or any field that is singularly unique? If so then you would store that field in your sub table with a single row for each response given

[Buakaw]

does your tblResponses have an autonumber field? Or any field that is singularly unique?

I don't think so. The keys by themselves are not unique.

RspnsID (the person who did the survey) by itself is not unique because many records will contain the same RspnsID, because each respondent (e.g. John) will answer many questions.

QstnID (which question is it) by itself will also not be unique, because many respondents (John, Bob, Jane) could take the same survey and answer the same question, so there will be multiple entries in the table with the same QstnID.

So the record in this table can only be uniquely identified by taking both keys together.

[rpeare]

Then I would change the design of that table and put in an autonumber field so you can uniquely identify a record by one field. dual primary keys make it very difficult to code particularly if you're new to the game.

Once you do that you can create a sub table that has the response table's unique identifier (if you are bound and determined to not change the structure of your existing tables you can still do what I'm suggesting but it's a better practice for each table to have a single field unique identifier). If you retain your current design your sub table would have to have all the fields that uniquely identify it on the parent table (tblResponses). Once the sub table is designed on your data entry for you can create a subform based on the sub table that links your primary key(s) field(s) to the parent form (parent table) then when you do data entry the key fields on your sub table will be populated with the parent table unique identifier(s).

[Buakaw]

Thanks! I took your advice and added a primary key field to the existing table.

I then created a subtable with 3 fields:
- an autonumber primary key field to uniquely identify each response
- an integer foreign key that stores the primary key of the existing table
- a text field to store the responses

I had to modify the form's SQL data source, but I tested it and it works!

Thank you very much!