Microsoft KB Archive/209193

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

Article ID: 209193

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209193



Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



MORE INFORMATION
The following steps show you how to create a sample query to return records from one table in a many-to-many relationship:  Open the sample database Northwind.mdb.  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.

