ACC2000: How to Return Records in a Many-to-Many Relationship (209193)


The information in this article applies to:

  • Microsoft Access 2000
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:
  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.

REFERENCES

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.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbhowto KB209193
©2004 Microsoft Corporation. All rights reserved.