DISTINCT query handles Nulls inconsistently

        4 votes: **        8,093 views      0 comments
by Allen Browne, 20 December 2005    (All versions of Access)

Flaws in Microsoft Access

Provided by Allen Browne, 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: Poor Your rating: Not so good Your rating: Average Your rating: Good Your 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...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.