UniqueRecords Property

Setting

The UniqueRecords property uses the following settings.

You can set the UniqueRecords property in the query's property sheet or in SQL view of the Query window.

Remarks

You can use the UniqueRecords property when you want to omit data based on entire duplicate records, not just duplicate fields. Microsoft Office Access 2007 considers a record to be unique as long as the value in one field in the record differs from the value in the same field in another record.

The UniqueRecords property has an effect only when you use more than one table in the query and select fields from the tables used in the query. The UniqueRecords property is ignored if the query includes only one table.

The UniqueRecords and UniqueValues properties are related in that only one of them can be set to Yes at a time. When you set UniqueRecords to Yes, for example, Access automatically sets UniqueValues to No. You can, however, set both of them to No. When both properties are set to No, all records are returned.

Example

The query in this example returns a list of customers from the Customers table who have at least one order in the Orders table.

Customers Table

Orders Table

The following SQL statement returns the customer names in the following table:

SELECT DISTINCTROW Customers.CompanyName, Customers.CustomerID
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;