Form Fields from several different tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do you display fields from several tables in a normalized DB? I'm new to
forms and my book only covers fields in the same table.

I have a test DB with Questions and Answers in 2 separate table. I would
like to be able to put the question in and then the 5 possible answers in the
Database at once that way i don't have to keep looking to see if i'm putting
the right answer in with the right question by switching from screen to
screen. Since there are no repeating groups, i will need to be able to repeat
a field also 5 times for each answer with its own AnswerID.

Thanks!
 
so you have tblQuestions and tblAnswers, correct? each question may have
multiple answers, and each answer belongs to only one question, correct?
that's a one-to-many relationship. (btw, i usually call the "available
answers" table, tblChoices or tblOptions, because i'm likely to have a
separate table for the actual answers entered by a user.) here's a standard
solution to modeling a one-to-many table relationship:

tblQuestions
QID (primary key)
QText
whatever other fields you need for the questions

tblChoices
CID (primary key)
QIDfk (foreign key from tblQuestions)
CText
whatever other fields you need for the answer choices

in the relationships window, link the two tables on the QID/QIDfk fields and
enforce referential integrity.

create a form bound to tblQuestions, this is your mainform - i'll call it
frmQuestions. create another form bound to tblChoices, this is your
subform - i'll call it frmChoicesSub (note: you do *not* need to have a
control on the form for the QIDfk field). open frmQuestions in design view.
add a subform control, i'll call it ChildChoices. set the control's
SourceObject property to frmChoicesSub. set the LinkChildFields property to
QIDfk. set the LinkMasterFields property to QID.

open the main form in form view. you can enter a new Question record, then
enter the answer choices in the subform. Access will automatically add the
QID value, from the Question record, to the QIDfk field of each Choice
record that you enter in the subform.

hth
 
Back
Top