Access SQL SELECT DISTINCT Clause

This Microsoft Access tutorial explains how to use SELECT DISTINCT Clause in Access Query.

Microsoft Access SQL SELECT DISTINCT Clause

SELECT DISTINCT can be used in Microsoft Access SQL to select unique values in a field. Note that you cannot use this keyword in Query Design View, you can only use it directly in SQL View.

The outcome of DISTINCT is exactly the same as GROUP BY if you only try to find the unique values, but GROUP BY can be used together with other Aggregate Functions such as COUNT, SUM, AVERAGE. In terms of performance, they should be the same or nearly the same.

Example 1 – Select one field with SELECT DISTINCT

Lets say we have an employee table as below, where Employee ID is an unique key.

Access SQL distinct 01

Create a Query and then switch to SQL View. Type the following SQL statement.

SELECT DISTINCT [First Name] from Table1

This statement means to select unique [First Name], which would be Peter and John. Run the SQL and view the result.

Access SQL distinct 02

Example 2 – Select two fields with SELECT DISTINCT

This time we try to evaluate the unique combination of First Name + Salary.

Type the following SQL statement. Note that we only need to use DISTINCT once to find the unique combination.

SELECT DISTINCT [First Name], [Salary]  from Table1

Access SQL distinct 03

Example 3 – Select two fields with WHERE Clause

Just like any other SQL, you can add a WHERE Clause in the end.

Type the below SQL statement which filters any Salary >=20000 first and then find the unique combination of First Name and Salary.

SELECT DISTINCT [First Name], [Salary] FROM Table1
WHERE [Salary] >= 20000

Access SQL distinct 04

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *