Creating a Many-to-Many Relationship in an Access Database

In this Access 2007 database tutorial, you will learn how to create a junction table in order to create a many-to-many relationship, where many employees can be related to many projects at once--all while maintaining primary keys and referential integrity.

For a more detailed explanation of many-to-many relationships, watch http://www.youtube.com/watch?v=JBG4SzNhA9A

Or you can visit us here: http://www.trainsignal.com/Microsoft-Office-Training.aspx?utm_source=YouTube&...

Category:

Howto & Style

Tags:

• Access 2007
• many to many relationship
• tutorial
• how to
• primary keys
• referential integrity
• database
• computers
• educational
• tips & tricks

[bestoOofriends]

hey ,, there is an idiot man that came to examen my class in access and he told us that both ids at the new table have to be foreign keys not PK .. is that right ?!!!

[TrainSignalOffice]

@bestoOofriends I'm not sure I understand the situation you are referring to or the question you are asking. I need more information.

[bestoOofriends]

@TrainSignalOffice he told us that it's wrong to put the fields at the new table(like tblprojectjunction) as PK ,,he said they have to be FK !!!

[TrainSignalOffice]

@bestoOofriends Well, in a few years, it sounds like he'll have a lot of data to sort through and clean up.

[TheKeefe95]

@TrainSignalOffice if it is a Primary Key from another table, in one table, isn't that known as a foreign key? I thought that a Primary Key, that isn't present in its original table, is always a foreign key?

[TrainSignalOffice]

@TheKeefe95 Yes, you are kind of correct. In this example, we are discussing how to create junction tables which in Access takes what appears to be the foreign keys (or primary keys from other tables) and designates the combination of those two fields as the table's primary key. Meaning the combination of information from the two fields becomes the table's unique identifier (or duel primary key). Does that help to clarify?

[christopherstruble]

@christopherstruble The new table that was created is considered the many to many and I would use the new table in my queries and in my forms is this correct? If so, I totally understand it now. If not I'm still confused!

[christopherstruble]

Please correct me if I'm wrong here, but within the relationships area shouldn't the lines that go from one table to another table be a straight line without the number 1 from one side and the infinity symbol on the otherside. Also when the dialog box opens from where you drag one field of one table to the same field of a different table I've noticed it said one to many both times between the EmployeeID and the Projectnum. Please help me to understand this.

[TheKeefe95]

@TrainSignalOffice Thanks for clarifying. It turns out I was kind of wrong anyway. Through my own fault.

[TheLtUhura]

Can you use a look-up field within a combinedPK?

[TheLtUhura]

Ah. Disregard. I was hoping to use the autonumber for the ID (not text), but autonumber was the problem.

[TheLtUhura]

just not clear on how to begin getting the data in with the many-to-many relationship. Every way I try gives me errors. If I enter directly into the original Table_Employees or TBL_Project, it does Not show up in the Junction table. If I start by entering data into the Junction table, it give me errors.

Also, could you speak to the difference in approach you advise here and the way advised by @djdates Access 2007 Tutorial 1.3. Many to Many Relationship

[bestoOofriends]

ahaa ,,thanxxx :)