Access Database Design

[Lee]

I want to create a database of Questions and their related information and link it to an other table of answers.

Each question would have 4 possible answers.

How do I relate it so that I can create a data entry form that will allow the user to add the question and the 4 answers for it?

I either get just one answer show up or it trys to relate the answer to all the questions.

If I were to do it in COBOL I would create a question record with a QuestionID and then an answer table with a twio field key QuestionID AnswerID (A B C or D)

Maybe I just don't know how to do this in Acess. I have an auto Number Answer ID which is unique but if I try to add a letter to the key it wants that to allow duplicates so there can be 1A, 2A , 3A etc. But then in the queries it associates answer A with Question 1, 2, 3 etc

Help!

[MMcCarthy]

Don't put the letter code in the AnswerID. In the answer table create a double primary key ( AnswerID, QuestionID) then add fields called QuestionLetter, Question. You can tie the answers to a question using the question ID and display in order of QuestionLetter.

I want to create a database of Questions and their related information and link it to an other table of answers.

Each question would have 4 possible answers.

How do I relate it so that I can create a data entry form that will allow the user to add the question and the 4 answers for it?

I either get just one answer show up or it trys to relate the answer to all the questions.

If I were to do it in COBOL I would create a question record with a QuestionID and then an answer table with a twio field key QuestionID AnswerID (A B C or D)

Maybe I just don't know how to do this in Acess. I have an auto Number Answer ID which is unique but if I try to add a letter to the key it wants that to allow duplicates so there can be 1A, 2A , 3A etc. But then in the queries it associates answer A with Question 1, 2, 3 etc

Help!

[MMcCarthy]

Sory my logic was backwards.

In the answer table create a double primary key ( AnswerID, QuestionID) then add fields called AnswerLetter, Answer.

In the question table just have QuestionID, Question.

By the way if you are displaying questions and answers on a form then
Design a form based on question table and have a subform based on answer table. Tie the forms together on QuestionID. In the subform you could have a continuous form which would show all the Answers for the question is focus. If you wanted you could add a check box to the answer table and allow users to check their answer.

Don't put the letter code in the AnswerID. In the answer table create a double primary key ( AnswerID, QuestionID) then add fields called QuestionLetter, Question. You can tie the answers to a question using the question ID and display in order of QuestionLetter.