databasedev.co.uk - database solutions and downloads for microsoft access

Designer for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available

Microsoft Access Many-To-Many Type Relationship

Managing Many-To-Many Relationships in Microsoft Access:

As mentioned earlier (See article on Identifying Table Relationships), RDBMSs do not support many-to-many relationships between tables. This is the example we had of this type of relationship:

EMPLOYEES
EmployeeID Last Name First Name ProjectNum
EN1-26 O'Brien Sean 30-452-T3
EN1-26 O'Brien Sean 30-457-T3
EN1-26 O'Brien Sean 31-124-T3
EN1-33 Guya Amy 30-452-T3
EN1-33 Guya Amy 30-482-TC
EN1-33 Guya Amy 31-124-T3
EN1-35 Baranco Steven 30-452-T3
EN1-35 Baranco Steven 31-238-TC
EN1-36 Roslyn Elizabeth 35-152-TC
EN1-38 Schaaf Carol 36-272-TC
EN1-40 Wing Alexandra 31-238-TC
EN1-40 Wing Alexandra 31-241-TC
PROJECTS
ProjectNum ProjectTitle EmployeeID
30-452-T3 Woodworking Around The House EN1-26
30-452-T3 Woodworking Around The House EN1-33
30-452-T3 Woodworking Around The House EN1-35
30-457-T3 Basic Home Electronics EN1-26
30-482-TC The Complete American Auto Repair Guide EN1-33
31-124-T3 The Sport Of Hang Gliding EN1-26
31-124-T3 The Sport Of Hang Gliding EN1-33
31-238-TC The Complete Baseball Reference EN1-35
31-238-TC The Complete Baseball Reference EN1-35
31-241-TC Improving Your Tennis Game EN1-40
35-152-TC Managing Your Personal Finances EN1-36
36-272-TC Using Electronic Mail Effectively EN1-38

Above, tables with a many-to-many relationship

The usual solution is to break this relationship down into two one-to-many relationships by creating an intersection or junction table. This table would hold the primary key field from each of the tables in the many-to-many relationship. In the new table, those fields together would be a multi-field primary key resulting in the following relationships and diagram.

An intersection table and two one-to-many relationships
An intersection table and two one-to-many relationships

This forms two one-to-many relationships; each employee can work on many projects and many employees can work on a single project. Just as you might have to make a connection in Chicago to fly from Boston to Seattle, you sometimes need an intermediate or junction table to get where you need to be.

Take a look at the following article that includes an example of Handling Many-To-Many Relationships in Microsoft Access