One to many design

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

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" wrote:

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

"REGREGL" wrote:

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" wrote:

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

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

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

""Tonka"" wrote:



"REGREGL" wrote:

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" wrote:

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

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