Creating a quiz database...

[Jimbo]

I"m trying to create a database of practice questions and answers for my
students, some of the questions have multiple answers. How should I go about
identifying (and storing) multiple correct answers to these questions? Any
help greatly appreciated.

 

[Allen Browne]

The basic structure would be something like this:

Question table: one record for each question.
QuestionID Number primary key.
QuestionText Text what the question says.

Answer table: one record for each possible answer.
QuestionID Number foreign key to Question.QuestionID
AnswerCode Text a for first, b for 2nd, etc.
AnswerText Text what this answer says.
AnswerValue Number 0 for wrong answer, 1 for right answer.
Primary key could be QuestionID + AnswerCode.

Then, if you want to record what answers people actually gave, you would
need:
Client table: one record for each person who takes the quiz.
ClientID AutoNumber primary key

ClientAnswer table: one record for each answer given by each person.
ClientID Number foreign key to Client.ClientID
QuestionID Number foreign key to Answer.QuestionID
AnswerCode Text foreign key to Answer.AnswerCode.
You could make the combination of ClientID + QuestionID unique (e.g. primary
key), and the relationship to the Answer table based on QuestionID +
AnswerCode.

 

[Guest]

hi Allen.
I tried the one you wrote, but i encounter errors on the AnswerCode and
AnswerValue Field. When i try to enter records/values an error appears on my
screen., it says "The changes you requestd to the table were not successful
because they would create duplicate values in hte index, primary key or
relatinship". Can you give me a detail instruction on how to do it? or
suggest me a site i can browse?
Thanks... Your help is greatly appreciated...