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

 Last reviewed: April 2, 1997
Article ID: Q106287
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

This article demonstrates 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 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.



REFERENCES

 


For more information about queries, search for "criteria, queries,
entering," and then "Enter criteria in a query or advanced filter to
retrieve certain records" using the Microsoft Access 97 Help Index.
 

	
	

	















	






Keywords            : GnlApp kbusage

Version             : 1.0 1.1 2.0 7.0 97

Platform            : WINDOWS

Hardware            : X86

Issue type          : kbhowto








THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.






	
	Last reviewed:  April 2, 1997
	

	© 1998 Microsoft Corporation. All rights reserved. Terms of Use.