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

[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?

 

[banem2]

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?

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]

like so...

table1
ID1 (PK)

table2
ID1 (FK)
ID2 (FK)

table2
ID2 (PK)

 

[John W. Vinson]

like so...

table1
ID1 (PK)

table2
ID1 (FK)
ID2 (FK)

table2
ID2 (PK)

Minor typo there - you need THREE different tables; not "table2" twice.

 

[TheTynus]

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???

 

[John W. Vinson]

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???

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>