How to Define a Many-to-Many Relationship in Microsoft Access

Video Transcript

"Hi my name is Chip Browne, I teach computer programming and Microsoft Office applications at Steven Henagar college here in Utah. I also create practice tests for Microsoft Office certifications. Today we are going to learn how to define a many to many relationship in Microsoft Office Access two thousand seven. Microsoft Access is a registered trademark of Microsoft Corporation. I am not affiliated with Microsoft Corporation. To edit many to many relationships it is important that we first understand how to view relationships in Microsoft Access database. Using this example database we'll go into database tools, and then in the show hide group we'll click on relationships. This displays all the relationships that have been setup between the tables in this sample database. Each of these squares represents a table with the fields. The key icon represents the primary key. The relationship is represented by the dark black line. The number one means that there is one table, one record in this table for every, and the infinity sign means many for many records in the orders table. It also means that all the orders have one and only one customer associated with it. In other words customers have many orders and each order belongs to one customer. This is a one to many relationship. To create a many to many relationship we have the situation that exists here between the employees table and privileges table. Employees have many privileges. Each privilege is mapped to one or more employees. To solve this many to many relationship, we introduce a new table and we can name it by the names of the two tables that it is the joined table for. The relationships that exist are a one to many and a many to one. Here we have two key fields. One is the key field for the employee ID and the other is the key field for the privileges. All the records here would indicate the relationships that exist between employees and privileges. Each entry in this would indicate be done for a particular employee and a particular privilege. This is how we create a many to many relationship. If you double click on the relationship it brings up the add a relationship dialog. This is where you would choose the table and the field and the related table and it's field. Likewise, table, the ID, the other table and this ID. This is how you create a many to many relationship. For more information or videos visit me Chip Browne at Chip Browne dot com. That's Browne with an e."