Foreign keys

[Fred Blair]

Access will not let me set more than one relationship between two tables.

I have three tables that try to describe a test.

Tests
tid autonumber
tname test name
tdate date modified

Questions
qid autonumber
tid long integer foreign key back to the Tests table
qtext text actual wording of the question
qans text correct answer
qdate date modified

Choices
cid autonumber
qid long integer foreign key back to question
tid long integer foreign key back to test
ctext text actual wording of the choice
cdate date modified

Each test will have 50 questions.
Each question will have 4 choices, standard multiple choice type test
Each question will have 1 correct answer

Am I headed in the right direction?

Thanks,
Fred

 

[tina]

in the Choices table, you don't need a foreign key back to the Test table.
each Choice record is linked to a Question record, and each Question record
is linked to a Test record - so you have an indirect, but clearly defined,
link between Choice records and Test records already.

suggest you download a copy of MVP Duane Hookom's AtYourSurvey db; after
studying his tables/relationships, i think you'll be able to answer your own
question.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

hth