Many to many relationships

[Tazzy via AccessMonster.com]

Hi All,

I hope someone can help me with this one please. I have a table of students
that contains many of their personal details. I've used an autonumber for a
primary key in this table. I also have another table that contains details
of numerous placements that they can attend, with a code used for a primary
key here. I now need to be able to link these tables showing details of
which students attend which placements, and I know that I need to create
another table to do this, but I'm having problems creating a suitable link.
One student can have a number of placements, but one placement can also have
a number of students.

I seem to remember something about having a join table to get around this,
but that's about all.

Once I have created this new table, I want to enter it into a tabbed sub-form
that is attached to the main student form. What I would like to be able to
do is when I enter the code for the placement, it then automatically fills in
the remaining details for that placement (name, address etc)

Anyone please lend me a helping hand on this?

Thanks in advance

 

[Larry Daugherty]

The general rule is "one issue per post". That way things are easier
to manage all around.

The term you're looking for is "junction table" The junction table
can consist of as little as the primary keys of the parent tables but
may contain information about the junction.

For your bonus question, your main form is presumed to be based on one
of the parent tables. The subform should have a combobox based on the
other parent table. Making the selection in the combobox can be made
to fill textboxes on the subform by putting some code in the
combobox's AfterUpdate event: references to controls on the subform
can get lengthy so I'll leave it up to you to research "referring to
controls on subforms" in either Access help or on www.mvps.org/access

Of course, the query on the table has to take in all of the fields you
need.

The gist of the code in the AfterUpdeate is
Me!MyControl=Me!MyCombobox.column(n). Note that when you refer to
columns in the combobox from the Access user interface, things are 1
base. When you refer to the same things in code they are 0 base.

 

[Jeff Boyce]

Tazzy

The generic approach to handling this is to first create the
"junction"/"resolver"/"relation" table that sits between the two you've
done. In this new table, you'll have every valid combination of student and
placement, using the ID from each, plus whatever other data is significant
about that relation. For example (just guessing here), you might want to
know that Jimmy was in Math, starting 5/1/2007, taught by Instructor Jones.
(notice that by adding instructor, you just used the table to resolve a
3-way many-many-many).

To handle the form, create a main form (probably for student-info), and a
subform based on the resolver table. You would use this design to first
pick a student, then, in the subform, pick a placement and add other
significant data (5/1/2007, InstructorID#17...).

Feel free to post back with more questions as you delve into this deeper.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Tazzy via AccessMonster.com]

Thanks to you both for your advice, will be trying this out over the next
couple of days, and I'll post back my progress (or lack of!)

 

[Tazzy via AccessMonster.com]

One point to clarify guys, when I create this junction table, it should
contain 2 one-to-many links, with both of the many's being on the junction
table?

 

[BruceM]

Correct. The primary key of each parent table is related one-to-many with a
corresponding foreign key in the junction table. Note that each parent
table's primary key is related to a different foreign key in the junction
table. The usual relationship rules apply (both fields of the same data
type, except Autonumber is related to a Number field, etc.).

 

[Amy Blankenship]

Tazzy

The generic approach to handling this is to first create the
"junction"/"resolver"/"relation" table that sits between the two you've
done. In this new table, you'll have every valid combination of student
and
placement, using the ID from each, plus whatever other data is significant
about that relation. For example (just guessing here), you might want to
know that Jimmy was in Math, starting 5/1/2007, taught by Instructor
Jones.
(notice that by adding instructor, you just used the table to resolve a
3-way many-many-many).

Although really that would be poor database design, since presumably this
instructor would be teaching the exact same class at the exact same time to
more than just Jimmy. Trying to store the instructor and date with the
student/class information would result in a lot of redundancy and confusing
data (what if Jimmy quit after one week, yet Mary took the whole class?).
So, you'd actually want to store all that information together and then link
to *that* in the junction table. You'd know that it was Math because the
Subject ID would be a foreign key in the Class table.

A better example of information about a junction that would be stored in the
junction table would be what seat Jimmy is assigned to for the semester for
that class. In other classes, Jimmy would have a different seat, and in
other semesters or other times of the same class, a different student is in
the same seat... So it relates only to the intersection between Jimmy and
Math 101 taught by Instructor Jones in Spring of 07 at 8 am on Tuesdays and
Thursdays.

HTH;

Amy

 

[Tazzy via AccessMonster.com]

Hi Amy,

Appreciate your help on this one, but this isn't to be set up for a whole
class to attend, maybe I didn't explain things properly. These aren't
classes as such, but placements outside schools for students to attend. For
example; student A could go to Placement 1 on a Monday for 4 weeks only,
Student B could go to the same placement, but say only on every other Tuesday.
Both could be doing different types of work and over a varying time span.
What I need to be able to do is just bring up specific references to each
student and their attendances, absences etc as opposed to a whole group. The
way the others have suggested is so far coming along nicely, but I am always
open to advice from those of you who are obvioulsy much more experienced at
this than me, I'm just glad to be able to use this forum to get the help I
need. One day I may even be able to post advice myself

 

[Amy Blankenship]

I'd do something like this:

tblStudents
StudentID
FirstName
LastName
Etc.

tblPlacementLoc
PlacementLocID
Description
Address
City
etc.

tblStudentPlacement
StudentPlacementID
StudentID
PlacementLocID
WorkType

tblStudentPlacementDates
StudentPlacementID
PlacementDate

The last table would have a date in it for every time that a student was
supposed to be in the placement in tblStudentPlacement. If the work type is
contingent on that, you'd move WorkType to that table. This is probably not
the best structure for you to use, since there's a lot of information I
don't know about your situation, but it might give you a jumping off point.

-Amy