Microsoft KB Archive/106287: Difference between revisions

From BetaArchive Wiki
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: >1
         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 "&gt;1" with a "Count" total on the same field returns only records with both products.
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

Knowledge Base


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

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:

  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: kbhowto KB106287