FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:
 

News Features

Total Visual SourceBook 2013 125,000+ lines of royalty-free module code for Microsoft Access, Office, VBA and VB6!

Royalty free Microsoft Access VBA/VB6 Module Code Library


Total Access Statistics 2013
Add advanced data and statistical analysis to Microsoft Access!

Microsoft Access Statistical Analysis


Get our Latest News

Email NewsletterLatest Newsletter (Sign up)

FMS Development Team BlogBlog with us and subscribe to our RSS feed.

FMS Facebook Page Like our Facebook Page

FMS Twitter Follow us on Twitter

Difference Between "Unique Values" (SELECT DISTINCT) and "Unique Records" (SELECT DISTINCTROW) in Microsoft Access Queries

Provided by: Molly Pell, Technical Project Manager

Introduction

In Microsoft Access, the SQL syntax of your query may say "Select Distinct" or "Select DistinctRow". Are you familiar with the difference? Learn how this impacts which records and number of records you see, and whether they can be edited.

Unique Values and Unique Records Properties

When creating a query in Microsoft Access, you might want to return only distinct or unique values. There are two options in the query's property sheet, "Unique Values" and "Unique Records":

Microsoft Access Query Property Sheet

These properties correspond with the DISTINCT and DISTINCTROW statements (respectively) in SQL.

DISTINCT versus DISTINCTROW SQL Keywords

DISTINCT and DISTINCTROW sometimes provide the same results, but there are significant differences:

DISTINCT

DISTINCT checks only the fields listed in the SQL string and then eliminates the duplicate rows. Results of DISTINCT queries are not updateable. They are a snapshot of the data.

DISTINCT queries are similar to Summary or Totals queries (queries using a GROUP BY clause).

DISTINCTROW

DISTINCTROW, on the other hand, checks all fields in the table that is being queried, and eliminates duplicates based on the entire record (not just the selected fields). Results of DISTINCTROW queries are updateable.

Example

Assume you have a simple orders database with the following data:

Customers

CustID

Company

City

1

ABC, Inc.

London

2

ABC, Inc.

Paris

3

Acme, Ltd.

New York

Orders

Order

CustID

Date

Product

1

1

6/1

Total Access Analyzer

2

1

6/2

Total Access Statistics

3

2

6/3

Total Access Detective

4

3

6/3

Total Access Emailer

You can see that ABC in London has 2 orders (CustID = 1), ABC in Paris has 1 order, and Acme has 1 order.

SELECT Query

Company

ABC, Inc.

ABC, Inc.

ABC, Inc.

Acme, Ltd.

A simple SELECT query like this:
SELECT Company
FROM Customers
INNER JOIN Orders ON Customers.CustID = Orders.CustID;

Returns one row for each Customer Order.

SELECT DISTINCT Query (Unique Values property = Yes)

Company

ABC, Inc.

Acme, Ltd.

A SELECT DISTINCT query like this:

SELECT DISTINCT Company
FROM Customers
INNER JOIN Orders ON Customers.CustID = Orders.CustID;

limits the results to unique values in the output field. The query results are not updateable.

SELECT DISTINCTROW Query (Unique Records property = Yes)

Company

ABC, Inc.

ABC, Inc.

Acme, Ltd.

A SELECT DISTINCTROW query like this:

SELECT DISTINCTROW Company
FROM Customers
INNER JOIN Orders ON Customers.CustID = Orders.CustID;

looks at the entire underlying tables, not just the output fields, to find unique rows.

Here, you see both ABC, Inc. companies (the one in London and the one in Paris), even though the output fields match. The query results are updateable because they correspond to the individual records in the Customers table.

Summary

So to summarize, use DISTINCT if you need a snapshot of certain data, without seeing duplicates in the results. Use DISTINCTROW if you want to return entirely distinct records, regardless of what’s shown by the output.

FMS Blog To discuss this further, visit our blog.

Note that Microsoft SQL Server supports DISTINCT, but not DISTINCTROW.
If you plan to migrate to SQL Server, do not rely on DISTINCTROW queries.


Additional Resources

Feedback

Contact Us  l   Web questions: Webmaster   l   Copyright © FMS, Inc., Vienna, Virginia
Celebrating our 28th Year of Software Excellence