When creating queries in Access, you may have noticed the query properties “Unique Values” and “Unique Records”. Are you familiar with the difference between these properties?
“Unique Values” and “Unique Records” correspond with the DISTINCT and DISTINCTROW statements (respectively) in the query’s SQL. Although they sometimes provide the same results, there are significant differences in how they work:
- DISTINCT checks for unique values only in the fields selected for output, and eliminates duplicate rows. Results are not updatable, since they do not necessarily correspond with a unique record.
- DISTINCTROW checks for unique values in all fields in the table that you are querying, not just the fields listed for output. If the table is keyed, the results are updatable, since they correspond with a single record in the underlying data.
Learn more and view an example of the differences in our new paper on Microsoft Access Queries: Distinct versus DistinctRow.
Visit our Microsoft Access Query Help Center for more query tips.
What happens if you want to sort the resulting table alphabetically?
SELECT DISTINCT Company FROM Customers INNER JOIN Orders ON Customers.CustID = Orders.CustID ORDER BY Company;
obviously would not work.
That order by clause should work fine to sort the results.
In the example, this would return the following:
Company
———-
ABC, Inc.
Acme, Ltd.
Let me know if this isn’t what you’re looking for and I’ll try to help.
What if you want to have unique values for a combo box and that is on a form to update another table. The DISTINCT doesnt allow your form to make updates. This is frustrating because I want a combo box based on known unique values but it cant be used on a form to update a table. HELP please.
Thanks