Although one-to-many is the most common type of relationship applied on tables, in some databases, you may need to create a relationship in which many records from one table
(A) can have many related records in another table (B) and vice versa. This type of relationship is known as many-to-many. For example, in our Video Collection database:
- It is possible to have one video that has many actors. In this case, if we had created a field to receive actors in the Videos table, we would enter many names of actors in that one field but this type of database would not be professional
- At the same time, one actor can have participated in many videos. In this case, if we had created a field to enter the titles of videos in the Actors table, the field would have too many entries, making the table unprofessional
To implement this type of relationship, you can create what is called a junction table. A junction table is a table whose main purpose is to bring together fields from other tables, creating a type of cross relationship for the necessary fields. A junction table can be very helpful for data analysis and
A junction table is usually made of three or four fields (usually not less than three and usually not more than four; a classic junction table has only three fields). The first field, almost less engaged, is used as the primary key, the same type of field almost every table has. The other fields hold data that would emanate from other tables.
You can create a junction table either in Datasheet View or in Design View:
- To create a junction table in Datasheet View, you use the Lookup Column menu that would call the Lookup Wizard. From the Lookup Wizard, select the table that holds the desired field, then select the field itself, and click Finish. After adding the first column, repeat the same steps for each needed field
- To create a junction table in Design View, set the field's data type to Lookup Wizard and proceed the same way you would for the Lookup Column
You can also switch to either view to create a junction table. In other words, you can create one field in one view and create the other field in the other view.