Microsoft KB Archive/106287: Difference between revisions
m (Text replacement - """ to """) |
m (Text replacement - ">" to ">") |
||
Line 76: | Line 76: | ||
Field Name: ProductID (or Product ID in versions 1.x and 2.0) | Field Name: ProductID (or Product ID in versions 1.x and 2.0) | ||
Total: Count | Total: Count | ||
Criteria: | Criteria: >1 | ||
Field Name: ProductID | Field Name: ProductID | ||
Total: Where | Total: Where | ||
Line 83: | Line 83: | ||
</pre></li></ol> | </pre></li></ol> | ||
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 " | 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. | ||
</div> | </div> |
Latest revision as of 16:48, 20 July 2020
Article ID: 106287
Article Last Modified on 1/18/2007
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q106287
Novice: Requires knowledge of the user interface on single-user computers.
SUMMARY
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:
- Open the sample database Northwind.mdb (NWIND.MDB in versions 1.x and 2.0).
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: kbhowto KB106287