quiz database

Question

I am trying to create a quiz database. I have 7 tables so far

tblTeam

TeamId - PK

TeamName

TblUserID

UserID - Pk

Username

TeamID- FK

TblQuizID

QuizID- PK

UserID-FK

ResponseID-FK

Date quiz taken

TblAnswers

AnswerID- PK

Answer fields for every answer

QuestionID-FK

TblQuestions

QuestionID-PK

QuestionDesc - for every question

TestID-FK

TblResponses

ResponseID-PK

TestID-FK

Response fields for every question

TblTestName

TestID-PK

TestName- description of the name of the test

I can't figure out how to relate the answers to the responses. I need to be able to compare the responses to the answer to determine how many questions each user got right.

Answer 1

Your strucutre is off. A survey database has a very specific structure.

You should NOT have a field for each question, but a record for each:

tblQuestion

QuestionID (PK)

Question

tblRespondent (this is similar to your User table)

RespondentID (PK)

FirstName

Lastname

TeamID

etc. 

tblResponse

ResponseID (PK)

RespondentID (FK)

ResponseDate

TestID (FK)

tblAnswer

AnswerID (PK)

ResponseID (FK)

QuestionID (FK)

Answer

So each record in tblAnswer represent an answer to a specific question and identifies the Response which tells you who took the quiz, which quiz and when

Answer 2

okay. That makes sense. I was trying to keep the responses for each test taker in one record. I am going to make the changes to my response table today.

I thought I read somewhere that I should have my answers in a separate tbl. Is it okay to leave them there? I hate the thought of typing the 75 answers over again. : )

Once I fix my tables, I am going to start on the forms. I might need a little help there.  Is there a good book that you know of on building a quiz database? I kind of got thrown in to this at work so I am struggling through with my limited Access knowledge.

Thanks for your help on the tables.

Lisa

Answer 3

OK, It looks like you are storing the question correctly, 1 question to a record. Your Answers table is storing the correct answers to the question, again one question to a record. But why don't you just have an Answer field in the Questions table?.

Your Responses table is analogous of my Answers table. And yes it is off. You should not have a field for each question but a record that identifies, the questionID, the test taker and the response to the question.

Answer 4

You might like to take a look at Questionnaire.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates a basic framework for survey type databases.  It's logical model is as below:

Answer 5

Okay. Sorry. I am confused.

Maybe an example

                                               description of question

 Record 1 QuestionId = 1     Question 1 = what is 10 + 2

Record 2  Question ID = 2    Question 2 = what is 10 - 3

Record  3 QuestionID = 3     Question 3 = what is 5+ 4

these are records in the answer table

AnswerID = 1   Answer 1 = 12

AnswerID = 2    Answer 2 = 7

AnswerID = 3   Answer 3 = 9

Responses ( person who took the test) in the response table are all fields not records

This is a field User1

This is a field 11

This is a field 7

This is a field 9

New record Responses ( another person who took the test)

Second row of response table

User2

12

7

9

Does that make sense? am I still off. thanks!