Prevent showing duplicate records in a query (MDB)
Microsoft Access considers a record to be unique when a value in any field in a record differs from the value in the same field in any other record. In a query, you aren't necessarily displaying all the fields that make up the records in the underlying tables or queries. Therefore, if the field that distinguishes one record from another isn't in the query design grid, the query's results can appear to include duplicate records.
For example, if one of the underlying tables for a query is an Employee table, and you add only the LastName and Country fields to the query design grid, it might appear that you have duplicate records when several employees have the same last name and live in the same country/region. However, the records are not duplicates in the underlying table because the EmployeeID is unique for each record.
- Open a query in Design view.
- Select the query by clicking anywhere in query Design view outside the design grid and the field lists.
- Click Properties
on the toolbar to display the query's property sheet. - Do one of the following:
Prevent showing duplicate records in a query based on fields in the underlying table or query
- Set the UniqueRecords property to Yes.
Prevent showing duplicate records in a query based on fields in the query design grid
- Set the UniqueValues property to Yes.
Notes
- The UniqueRecords property has an effect only when you use more than one table in the query and select fields from those tables. When the UniqueRecords property is set to Yes, Microsoft Access automatically sets the UniqueValues property to No.
- When the UniqueValues property is set to Yes, Microsoft Access automatically sets the UniqueRecords property to No.
- To view the query's results, click View
on the toolbar.