Flaws in Microsoft Access

Provided by allenbrowne.com, December 2005

DISTINCT query handles Nulls inconsistently

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.

Steps to reproduce

1. Open Northwind.
2. Create a query, choosing the Customers table.
3. Switch to SQL View, and paste in the SQL statement above.
4. Switch to Datasheet view to see the results. The first row is Null.
5. Save the query with a name such as Query1. Close.
6. Open the Customers table in design view.
7. Select the Fax field.
8. In the lower pane, set the Indexed property to: Yes, No Duplicates.
9. Save. Close the table.
10. Open Query1 again.

The query now returns some twenty-six Null rows. The same query gives 25 more records than it did at step 4.