ACC2000: How to Return Records in a Many-to-Many Relationship

This article shows you how to design a query to return the records from one table in a many-to-many relationship where each of the records returned is related to two records in the other table in the relationship. Note that the many-to-many relationship between the two tables is broken into two one-to-many relationships with a third (junction) table.

The following steps show you how to create a sample query to return records from one table in a many-to-many relationship:

1. Open the sample database Northwind.mdb.
2. Create the following new query based on the Order Detail table:

      Query: Test
      ----------------------
      Type: Select Query
   
      Field Name: OrderID 
         Total: Group By
      Field Name: ProductID 
         Total: Count
         Criteria: >1
      Field Name: ProductID
         Total: Where
         Show: False
         Criteria: 14 or 21
   					

Note that the use of the criteria "14 or 21" with a "Where" total on the ProductID field returns only those order IDs that contain either one or both of the products. The use of the criteria ">1" with a "Count" total on the same field returns only records with both products.

For more information about queries, click Microsoft Access Help on the Help menu, type enter criteria in a query or advanced filter to retrieve certain records in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.