From the help file:
DISTINCT:
Omits records that contain duplicate data in the selected fields. To be
included in the results of the query, the values for each field listed in
the SELECT statement must be unique. For example, several employees listed
in an Employees table may have the same last name. If two records contain
Smith in the LastName field, the following SQL statement returns only one
record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;
If you omit DISTINCT, this query returns both Smith records.
If the SELECT clause contains more than one field, the combination of values
from all fields must be unique for a given record to be included in the
results.
The output of a query that uses DISTINCT is not updatable and does not
reflect subsequent changes made by other users.
DISTINCTROW:
Omits data based on entire duplicate records, not just duplicate fields. For
example, you could create a query that joins the Customers and Orders tables
on the CustomerID field. The Customers table contains no duplicate
CustomerID fields, but the Orders table does because each customer can have
many orders. The following SQL statement shows how you can use DISTINCTROW
to produce a list of companies that have at least one order but without any
details about those orders:
SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;
If you omit DISTINCTROW, this query produces multiple rows for each company
that has more than one order.
DISTINCTROW has an effect only when you select fields from some, but not
all, of the tables used in the query. DISTINCTROW is ignored if your query
includes only one table, or if you output fields from all tables.
Basically, the difference is whether the entire record is distinct
(including fields not returned by the query) or if just the output of the
query has no duplicates.
--
Wayne Morgan
MS Access MVP
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
news:GQ****************@newssvr21.news.prodigy.com ...
Can someone explain the difference between these 2 queries?
"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"
Thanks,
Fred Zuckerman
