DISTINCT query handles Nulls inconsistently

        4 votes: *****     8,304 views      No comments
by Allen Browne, 20 December 2005    (for ALL VERSIONS of Access)

Flaws in Microsoft Access

Provided by allenbrowne.com, December 2005

DISTINCT query handles Nulls inconsistently

Query screenshot

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.

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'DISTINCT query handles Nulls inconsistently'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).