Access, how do I create a many to many relationship?

  • Thread starter Thread starter TheTynus
  • Start date Start date
T

TheTynus

One table for Job numbers, with the form "12345"
One table for Items, with the form "AB123C"
Joining table for items for Jobs, with the form "12345";"AB123C";"Number of
items"
how do I go about it?
 
One table for Job numbers, with the form "12345"
One table for Items, with the form "AB123C"
Joining table for items for Jobs, with the form "12345";"AB123C";"Number of
items"
how do I go about it?
Click to expand...

Using third table. Third table should contain ID fields from both
tables and both tables should link to this 3rd table - they are not
connected directly.

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
scubadiver
thank you: I have tried this. However, in the relationships window, when I
enforce referential integrity and then look at the relationship diagram I get
tbl1 one to many tbl3 and tbl2 one to many tbl3 not what I want to see which
is tbl1 many to one tbl3 and tbl2 many to one tbl3. what am I doing wrong???
 
scubadiver
thank you: I have tried this. However, in the relationships window, when I
enforce referential integrity and then look at the relationship diagram I get
tbl1 one to many tbl3 and tbl2 one to many tbl3 not what I want to see which
is tbl1 many to one tbl3 and tbl2 many to one tbl3. what am I doing wrong???
Click to expand...

Let's clear up a bit of confusion here. For one thing, it makes a lot more
sense if you use meaningful tablenames rather than the default (and easily
confused even without typos) Table1 and so on.

A classic example is a school enrollment model: each Student can take zero,
one, or many Classes, and each Class can enrol zero, one or many Students. The
"resolver table" in this example could be called Enrollment:

Students
StudentID <PK>
LastName
FirstName
<etc>, info about the student as a person

Classes
ClassNo <PK>
ClassName
<other info about the class as an entity>

Enrollment
StudentID <FK to Students, students 1:n Enrollment>
ClassNo <FK to Classes, Classes 1:n Enrollment>
 
Back
Top