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

MORE INFORMATION

The following steps demonstrate 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 (NWIND.MDB in versions 1.x and 2.0).
2. Create the following new query based on the Order Details table:
   

         Query: Test
         ---------------------------------------------------------
         Type: Select Query
   
         Field Name: OrderID (or Order ID in versions 1.x and 2.0)
            Total: Group By
         Field Name: ProductID (or Product ID in versions 1.x and 2.0)
            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.