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.
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.
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
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
