Part II: Programming Microsoft Access
478
When you create a SQL SELECT statement, several predicates are available for the SELECT clause:l ALLl (^) DISTINCT
l DISTINCTROW
l (^) TOP
A predicate modifies how the SELECT command works. It works in conjunction with the WHERE
clause (actually, in SQL terminology, the WHERE condition) of a SQL statement.
ALL
As its name implies, the ALL predicate means return all records matching the query’s criteria. ALL
is the default for Select queries.
DISTINCT
Use the DISTINCT predicate when you want to retrieve only one instance of duplicated data in
the fields specified in the SELECT statement. For example, assume you want to know all the differ-
ent cities where the Collectible Mini Cars customers live. The following SQL statement queries
tblCustomers for the distinct City values:
SELECT DISTINCT City
FROM tblCustomers
Adding an ORDER BY clause ensures the returned data is properly sorted:
SELECT DISTINCT City
FROM tblCustomers
ORDER BY City
By default, a query containing the DISTINCT clause is sorted by the selected fields, but it’s always
a good idea to include an explicit ORDER BY clause.
I’ll ignore everything past the first line of this query for the meantime. Notice the DISTINCT
clause that follows the SELECT keyword. The addition of the DISTINCT keyword has a profound
effect on the records returned by this query.
The DISTINCT predicate tells Access to show only one record if the values in the selected fields are
duplicates (in this case the selected field is City). Other fields that are not included in the query
may be different. DISTINCT eliminates duplicates based on the fields selected by the query.
The DISTINCT predicate is added to an Access query’s SQL statement by setting the query’s
Unique Values property. Right-click in the upper portion of the Access Query Designer, and
select Properties. Then, set the Unique Values property to Yes (see Figure 13.3). Access adds
the DISTINCT predicate to the SQL statement underlying the query for you.