Microsoft KB Archive/103133
ACC1x: DISTINCTROW Keyword Ignored in a Cartesian Product
The information in this article applies to:
- Microsoft Access 1.1
Your query dynaset includes duplicate records, which should have been eliminated by the presence of the keyword DISTINCTROW.
This problem occurs if no join is specified between the tables in the query.
To hide the duplicate records, verify that the query output includes one field that uniquely identifies each record, then replace the keyword DISTINCTROW with the keyword DISTINCT.
Microsoft has confirmed this to be a problem in Microsoft Access version 1.1. This problem no longer occurs in Microsoft Access version 2.0.
Steps to Reproduce Behavior
- Open the sample database NWIND.MDB.
- Create a new query based on the Customers and Orders tables. Join the tables on the Customer ID field.
- Drag the Contact Name field to the query grid and run the query. Note that your dynaset contains 90 records.
- Switch to Design view and delete the join line between the tables.
- Add the following criteria in the Contact Name field on the query grid:
Customers.[Customer ID] = Orders.[Customer ID]
- Run the query. The dynaset now contains 1078 records. Contact names from companies that have placed multiple orders are displayed multiple times.
Search for "ALL," "DISTINCT," "DISTINCTROW," and "Predicates (SQL)" using the Microsoft Access Help menu.
Microsoft Access "User's Guide," version 1.1, page 187
Additional query words: relationship many-to-many duplicates
Keywords : kbusage
Issue type : kbbug
Last Reviewed: November 4, 2000