Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 209193 - Last Review: January 26, 2005 - Revision: 1.3
ACC2000: How to Return Records in a Many-to-Many Relationship
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).
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:
- 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.
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate