ACC97: Changes with DISTINCTROW in Microsoft Access 97

Microsoft Access 97 and Microsoft Jet database engine version 3.5 use the DISTINCTROW predicate differently than did earlier versions. This article discusses the differences in queries that use DISTINCTROW.

The DISTINCTROW predicate is the SQL equivalent of setting the UniqueRecords property in a query to Yes. In Microsoft Access 97, the default setting for the UniqueRecords property in a query is No, which eliminates DISTINCTROW from the query's SQL statement. In Microsoft Access 2.0 and 7.0, the default setting for the UniqueRecords property is Yes, which adds DISTINCTROW to the query's SQL statement.

Changes in the Microsoft Jet database engine included with Microsoft Access 97 cause the program to handle the SQL reserved word, DISTINCTROW, differently than it did in earlier versions. Three types of queries are affected by the change: update queries, delete queries, and select queries.

NOTE: Using DISTINCTROW in a query has no effect unless the query contains two or more joined tables.

Update Queries

If you run an update query that you imported from a Microsoft Access 2.0 or 7.0 database, or if you run an existing update query in a database that you converted in Microsoft Access 97 from an earlier version, you may receive the following error message:

To resolve this error message, set the query's UniqueRecords property to No, as follows:

1. Open the update query in Design view.
2. If the property sheet is not already open, on the View menu, click Properties.
3. Click an empty area in the upper half of the query window so the property sheet displays Query Properties in the title bar.
4. Set the UniqueRecords property to No.
5. Save the query, close it, and then run the query.

Delete Queries

When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the UniqueRecords property must be set to Yes for all versions of Microsoft Access. However, because the default value for UniqueRecords is No in Microsoft Access 97, you must set the value of this property manually when you create a new delete query in Microsoft Access 97, as follows:

1. Open the delete query in Design view.
2. If the property sheet is not already open, on the View menu, click Properties.
3. Click an empty area in the upper half of the query window so the property sheet displays Query Properties in the title bar.
4. Set the UniqueRecords property to Yes.
5. Save the query, close it, and then run the query.

For more information about using a delete query to remove duplicate records, please see the following article in the Microsoft Knowledge Base:

98664  (http://support.microsoft.com/kb/98664/EN-US/ ) ACC: Deleting Duplicate Records from Two Tables

Select Queries

Some select queries that are updateable in Microsoft Access 2.0 or 7.0 may display the following information on the message line when you try to update them in Microsoft Access 97:

To resolve this error message, set the query's UniqueRecords property to No, as follows:

1. Open the select query in Design view.
2. If the property sheet is not already open, on the View menu, click Properties.
3. Click an empty area in the upper half of the query window so the property sheet displays Query Properties in the title bar.
4. Set the UniqueRecords property to No.
5. Save the query, close it, and then run the query. Note that Microsoft Access 97 allows you to update the data in the query.

For more information about the DISTINCTROW and DISTINCT predicates, search the Help Index for "DISTINCTROW predicate" or "DISTINCT predicate."