Provided by Allen Browne, December 2005
You can ask Access to return just the unique values from a field, by setting Unique Values to Yes in the query properties. Access adds the DISTINCT predicate to the SQL View of the query, so the example pictured becomes:
SELECT DISTINCT Customers.Fax FROM Customers ORDER BY Customers.Fax;
If some rows are Null, the query naturally shows one Null row.
However, if the field is indexed "No Duplicates", DISTINCT does not work correctly. Access fails to de-duplicate the Nulls, and returns a row for every Null record in the table.
The number of records returned by the query is therefore inconsistent, depending how the field is indexed. You cannot trust DISTINCT to de-duplicate correctly, if any of the fields might contain a Null.
This problem exists in all versions of Access.
The query now returns some twenty-six Null rows. The same query gives 25 more records than it did at step 4.
Home | Index of tips | Top |