One to many design

[Guest]

I have two tables. The first one is tblStu, that has StuId as a primary
key(PK), a TeacherId non-PK, and other stud-related fields. The second one is
tblTeacher with TeacherID is a PK and other teacher-related fields.
I have a form that bounds to this tblStu and a combo box that bounds to
tblTeacher. Originally, a student is assigned to a teacher. However, I'd like
to re-design my form so that a student may be assigned to more than one
teacher. My questions are following.
1. Do I need to create a new (second) combo box to help a user to select
another teacher? What should I do?
2. Should I make change to the tblStu structure, i.e., add a second PK to
TeacherID in tblStu besides StuID?
Thank you for your help in advance

 

[Guest]

I would create a 3rd table (stu/tchr combo) with student id and teacher id as
the fields. create a form to use as a subform on the student form.
associate the student ID between the two forms and utilize a drop down box
for selecting the teacher. This form would present the student with all of
their assigned teachers. You could also utilize a similar sub-form on a
Teachers main form to present each teacher with all of their students.

But that's just what I would do.

 

[Guest]

I would create a 3rd table (stu/tchr combo) with student id and teacher id as
the fields. create a form to use as a subform on the student form.
associate the student ID between the two forms and utilize a drop down box
for selecting the teacher. This form would present the student with all of
their assigned teachers. You could also utilize a similar sub-form on a
Teachers main form to present each teacher with all of their students.

But that's just what I would do.

Tim,

Dont worrie to much about your form, in the first instance you need to get
your table design correct, it seems to me you need to create a one to many
relationship before you start designing your forms, i.e One teacher Many
Students, Or One Student Many Teachers, their is a way around this by
creating a bridge table Many to Many, the structure should have 3 tables
tblStu tblBridge tblTeacher, the structure should be 1 tblSu many tblBridge &
1 tbleTeacher many tblBridge, once you have done this you can start building
your forms...

Have fun

Tonka

 

[Guest]

Thank you both of you. Smart guys! I understand what you guys mean. But I
already have all data in both the tblStud and tblTeacher and now I need to
create the combo tblStuTea. But how do I add the data that are already in
tblStud and tblTeacher. Thanks