ACC1x: DISTINCTROW Keyword Ignored in a Cartesian Product

• Microsoft Access version 1.1

SYMPTOMS

Your query dynaset includes duplicate records, which should have been eliminated by the presence of the keyword DISTINCTROW.

CAUSE

This problem occurs if no join is specified between the tables in the query.

RESOLUTION

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.

STATUS

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.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Create a new query based on the Customers and Orders tables. Join the tables on the Customer ID field.

3. Drag the Contact Name field to the query grid and run the query. Note that your dynaset contains 90 records.

4. Switch to Design view and delete the join line between the tables.

5. Add the following criteria in the Contact Name field on the query grid:

         Customers.[Customer ID] = Orders.[Customer ID]
   

6. Run the query. The dynaset now contains 1078 records. Contact names from companies that have placed multiple orders are displayed multiple times.

REFERENCES

Search for "ALL," "DISTINCT," "DISTINCTROW," and "Predicates (SQL)" using the Microsoft Access Help menu.

Microsoft Access "User's Guide," version 1.1, page 187