Many to Many query

  • Thread starter Thread starter Archosu
  • Start date Start date
A

Archosu

I am tring to build a query that has two tables that many
to many relationship. I am returning many selections for
each record. I have heared of a intersection table. Will
this help me return a single entity for each record and
how are they set up?


Thanks
 
I am tring to build a query that has two tables that many
to many relationship. I am returning many selections for
each record. I have heared of a intersection table. Will
this help me return a single entity for each record and
how are they set up?
Click to expand...

A many to many relationship requires a third table to contain the
information about which record in TableA is related to which records
in TableB.

A simple example is a school-enrollment database. A school has many
Students; it also has many Classes. Each student takes several (but
not all!) classes, and each class has several students.

To depict this relationship, you need a third table: let's call it
Enrollment. The tables would have fields like:

Classes
ClassNo Primary Key
ClassName
<other info about the class, e.g. when and where it meets>

Students
StudentID Primary Key
LastName
FirstName
<other bio information>

Enrollment
ClassNo <link to Classes>
StudentID <link to Students>

If a student is enrolled in five classes, there would be five records
with that student's ID in the Enrollment table, each linked to one of
those five classes.
 
1. Open Access Help. And look for junction table. It is
well explained there :)
2. Read the next:

The many-to-many relationship
The many-to-many relationship is the hardest to
understand. Think of it generally as
a pair of one-to-many relationships between two tables,
with a special table created
(called a junction table) that is used to link them
together. The junction table is
comprised of a minimum of two fields - the foreign keys
from both tables it is linking
together. These two fields are subsequently used to create
the primary key in
the junction table. This junction table could easily be
created in the case of the
tables Pets and Visits Details in the Mountain Animal
Hospital database - with the
Visits table, by simply making the primary key a
combination of the Visit Number
and Pet ID.
A pet can be serviced at the hospital on many dates, so
you see a one-to-many relationship
between Pets and Visits Details. On the other hand, each
medication or
type of treatment can be given or performed on many Pets;
this is also a one-tomany
relationship. Thus a pair of separate, two-way, one-to-
many relationships creates a many-to-many relationship -
for a true many-to-many relationship
between these two tables, the Visits table (acting as a
junction table) would have to
have a primary key of Pet ID and Visit Number in the
junction table.

Source: Access XP Bible. That book is realy GREAT!
 
Back
Top