databasedev.co.uk - database solutions and downloads for microsoft access
Microsoft Access Articles
- General Microsoft Access Articles
- Microsoft Access 2007 Articles
- Tables
- Queries
- SQL
- Forms
- Reports
- Macros
- Modules & VBA
- Data Models
- Downloads
GUI Design
Resources
Online Shop
Info
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
Handling Many-To-Many Relationships in Microsoft Access:
Definition: Many-to-many (M:M) - is used to relate many records in the table A with many records in the table B. A record ('parent') in Table A can have many matching records ('children') in Table B, and a record ('child') in Table B can have many matching records ('parents') in Table A.
It is the hardest relationship to understand and it is not correct. By breaking it into two one-to-many relationships and creating a new (junction/link) table to stand between the two existing tables will enable correct and appropriate relationship setting.
A many-to-many relationship is really two one-to-many relationships with a junction/link table.
NOTE: Link table usually has the composite Primary Key that consists of the Foreign Keys from both tables A and B.
The associated database download includes an example of this.
The data relates to storing information about Artists (Musical) and Music (Albums, Singles etc).
The relationships can be seen below:
The relationships for the Artists and Albums database
In this example, ONE ARTIST can have ONE or MANY ALBUMS and an ALBUM can have ONE or MANY ARTISTS related to it.
This can be especially applicable if the ALBUM is a COMPILATION of many artists.
The ARTIST table consists of only the Artist ID and the Artist Name (Full name).
The ALBUM table consists of the Album ID, Album (or Single) name the Genre ID (lookup to the Genre table), the Label ID (lookup to the Label table) and the Album Release Date.
The two tables are linked together by the LINK_Artist_Album table. This table contains a composite Primary Key containing the Artist ID and the Album ID.
The database contains 2 main forms, where you can view Albums relating to each Artist and Artists relating to each Album.
Viewing the Artist and their related Albums
Viewing the Album and their related Artists
Hopefully this may make understanding the use of a Many-To-Many database relationship a little more straight forward.
Please download the Microsoft Access 2000 database example file from the Microsoft Access Tables page or from the Microsoft Access Downloads index.