Part II: Programming Microsoft Access
480
The DISTINCTROW predicate is added to a query’s SQL statement by setting the query’s Unique
Records property to Yes. Unique Values (DISTINCT) and Unique Records
(DISTINCTROW) are mutually exclusive and both cannot be set to Yes at the same time. The
Chapter13.accdb example database includes qryUsing_DISTINCTROW, a query that demon-
strates the DISTINCTROW predicate.
TOP
The TOP predicate enables you to restrict the number of records returned to the TOP <number> of
values. For example, the following SELECT statement displays the first five contact records (see
qryTop_5_Sales in Chapter13.accdb):
SELECT TOP 5
tblSales.InvoiceNumber, tblSales.SaleDate, tblSales.CustomerID,
tblSales.InvoiceAmount
FROM tblSales;
You must use the ORDER BY clause in conjunction with the TOP predicate. This example (qryCu-
stomersMostRecentSales) uses the ORDER BY clause with the TOP predicate to answer a
business question (which five companies most recently place orders):
SELECT TOP 5 Company, LastSalesDate
FROM tblCustomers
ORDER BY LastSalesDate DESC
This example returns a list of companies with the five most recent sales dates. In other words, the
query lists all the companies and orders them by their last sales date in descending order (so that the
most recent sales are at the top of the list), and then picks the first five companies in the ordered list.
A TOP query does not always return exactly the number of records specified. The criteria used may
return fewer records than requested, or more records may be returned if multiple records match the
criteria and the “tied” records push the count record count over the requested number.
The TOP predicate has an optional keyword, PERCENT, that displays the top number of records on
the basis of a percentage rather than a number. To see the top 10 percent of your contacts, you use
a SELECT statement like this example (qryCustomersTop10PercentSales):
SELECT TOP 10 PERCENT Company, LastSalesDate
FROM tblCustomers
ORDER BY LastSalesDate DESC
FROM
As the name suggests, the FROM clause specifies the tables (or queries) that contain the fields
named in the SELECT statement. The FROM clause is required for SELECT queries. The FROM
clause tells SQL where to find the records. If you fail to include a FROM clause in a SELECT state-
ment, you’ll receive an error.
When you’re working with one table, the FROM clause simply specifies the table name:
SELECT Company, City
FROM tblCustomers
