Table of contents
TOC
Collapse the table of content
Expand the table of content

UniqueRecords Property

office 365 dev account|Last Updated: 4/3/2018
|
3 Contributors

Applies to: Access 2013 | Access 2016

In this articleSettingRemarksExampleCustomers tableOrders table

You can use the UniqueRecords property to specify whether to return only unique records based on all fields in the underlying data source, not just those fields present in the query itself.

Note The UniqueRecords property applies only to append and make-table action queries and select queries.

Setting

The UniqueRecords property uses the following settings.

SettingDescription
YesDoesn't return duplicate records.
No(Default) Returns duplicate records.

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

Note You set this property when you create a new query by using an SQL statement. The DISTINCTROW predicate corresponds to the UniqueRecords property setting. The DISTINCT predicate corresponds to the UniqueValues property setting.

Remarks

You can use the UniqueRecords property when you want to omit data based on entire duplicate records, not just duplicate fields. Microsoft Access 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, Microsoft 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

Company nameCustomer ID
Ernst HandelERNSH
Familia ArquibaldoFAMIA
FISSA Fabrica Inter. Salchichas S.A.FISSA
Folies gourmandesFOLIG

Orders table

Customer IDOrder ID
ERNSH10698
FAMIA10512
FAMIA10725
FOLIG10763
FOLIG10408

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; 
Customers returnedCustomer ID
Ernst HandelERNSH
Familia ArquibaldoFAMIA
Folies gourmandesFOLIG

ACCESS SUPPORT RESOURCES
Access for developers forum on MSDN
Access help on support.office.com
Access help on answers.microsoft.com
Search for specific Access error codes on Bing
Access forums on UtterAccess
Access wiki on UtterAcess
Access developer and VBA programming help center (FMS)
Access posts on StackOverflow

© 2018 Microsoft