DISTINCT v DISTINCTROW
By Richard Rost
6 months ago
Unique Values v Unique Records in Access Queries
In this Microsoft Access tutorial I will show you how to use the DISTINCT and DISTINCTROW options in queries to get unique values versus unique records, explain the difference between them, and show you practical examples of when to use each, including how they behave when joining multiple tables.
Sabrina from Columbus, Ohio (a Platinum Member) asks: I see Unique Values and Unique Records in the query property sheet. What's the difference between them, and when should I use one over the other?
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, DISTINCT vs DISTINCTROW, unique values query, unique records query, SQL SELECT DISTINCT, SQL SELECT DISTINCTROW, query property sheet unique values, query property sheet unique records, differences between DISTINCT and DISTINCTROW, aggregate query alternative, Access SQL queries, joining tables with DISTINCTROW, unique customer list, Access query performance, removing duplicate records, primary table unique records, multi-table join queries, group by vs DISTINCT, efficient queries on large databases, SQL Seminar Part 1 topics, Access query optimization
Transcript
Today we are going to talk about DISTINCT and DISTINCTROW, otherwise known as unique values versus unique records. I am going to show you what they are for, how to use them, and why they are good.
Today's question comes from Sabrina in Columbus, Ohio, one of my Platinum members. She says, I see unique values and unique records in the query property sheet. What is the difference between them, and when should I use one over the other?
These are kind of seldom-used properties. Honestly, I do not use them that much myself. I pretty much stick to aggregate queries, even though these are definitely better. So I am probably going to start using these more in my toolkit as well, but let us go over real quick what they mean.
First up, this is an expert-level video. What does that mean? Well, that is sandwiched between beginner and developer, so it is like middle ground intermediate. It is too long to spell, so I call them expert level. That means you are beyond the basics, but we do not need programming for this. This is all straight SQL.
With that being said, if you are not very comfortable with SQL, go watch this first. This talks about how to use the SQL language in Access. I will be honest, I did not consider myself an expert with SQL either until I had been using Access for maybe 10 years. I was mostly just using the Query By Example grid and building forms and tables and all that. I knew VB because I was a BASIC programmer, but I did not learn SQL properly until I had been using Access for well over a decade. Go watch this; it will get you started.
Also, go watch my aggregate query video. Aggregate queries are what I use most of the time, even though DISTINCT and DISTINCTROW are more efficient. Aggregate queries are sometimes easier; they are usually easier. Go watch both of these. These are free videos. They are on my website. They are on my YouTube channel. Go watch those and come on back.
Here I am in a TechHelp free template. This is a free database you can grab off my website if you want to; you will find a link down below. In here, I have got a bunch of customers. Let us make things easier for class. I am going to get rid of all but five customers. Just delete all those guys. Now I am going to add myself again as another customer, just so we can get a distinct list of our customers.
Normally, if I wanted to do this, I would create a query and bring in the customer table. Let us say you just want a unique list of customer names, so I will bring in first name and last name, and then I will run it. I see two Richard Rost. To make a unique list, we will just turn Totals on (Group By aggregate query), and it will group by the ones that are the same. If I run it, there is my unique list of customers.
That is just fine if that is all you want to do. Honestly, an aggregate query is overkill for that because there is a lot of overhead behind an aggregate query. Let us turn that off. Let me show you a better way. If that is all you want - you are not going to be doing any calculations, like normally you can do a sum or a group by or a count over here - if you do not want that, what you can do is go into the table properties. What I do is right-click back here, go to Properties, or you can turn on the property sheet up here, either one. Make sure you click on the background here so you get the query properties.
There is a bunch of stuff in here, and yes, I cover all of the stuff in my Access Expert course. But two values we are looking for today are Unique Values and Unique Records.
Unique Values is the same as DISTINCT, and Unique Records is the same as DISTINCTROW. We are going to talk about what these mean in a second. If you use Unique Values, turn that on, set that to Yes. It is going to be the same thing that we just had a minute ago with that aggregate query. If I run it now, there is my unique list of customer names.
The benefit is that Unique Values, or DISTINCT, if you come in here and go to SQL View, you will see it says SELECT DISTINCT. What DISTINCT does is much quicker than an aggregate query. It is much faster internally than a Group By because all it is literally doing is just giving you one of each unique record. It does not have to worry about count and sum and all those other things. Those actually slow it down internally. When you have a database this small that is sitting locally, you are not going to notice the difference, but if you have a big database on the network, this will run faster.
How is this different from DISTINCTROW? Well, let us turn on Unique Records. Notice only one of these can be on at a time. Unique Records makes that say DISTINCTROW right there. What does that do? Let us take a look.
Hit Run. Now, look, I see both Richard Rosts again.
Why is that? DISTINCTROW looks at the entire row in the table underneath. Every single field has to be identical, even though you only brought these two up here, including the autonumber. That is why DISTINCTROW is called unique records - the whole record has to be identical. In this case, 1 and 34 are not the same, so those are two distinct records. Same thing with all the other fields: the email address, etc. Even if I copied this whole record down here, it is still going to have a different autonumber. Those are two distinct records.
Now, why would you want to do that? If 99.99 percent of all of your tables are going to have an autonumber in them, you are almost never going to come up with a completely unique record. That is true. But where DISTINCTROW shines is when you join multiple tables together.
Let us say, hypothetically, let me minimize this for a second. Let us say here, I am going to get rid of the duplicate me, so there is just one Richard Rost now.
Let us take a look at the orders of these customers. Orders here: I have got two orders for me. Let us take a look at Jim Kirk. He has got two orders. I am going to add another order for him. I just put any old date in here and some stuff, etc. So I have got two orders, Jim Kirk has got two orders.
If you look at it now - all the other customers are still in here - let us sort by customer ID. I did not sort, I filtered. Right-click, Sort. So I have two orders, and Jim Kirk has two orders. Now, let us say I wanted to get a unique list of customers that have orders.
Again, you can do this with an aggregate query. I am just showing you more Legos, different tools for your box. Add orderT to the query. Make sure they are joined properly.
For the sake of class again, I am going to turn that Unique Records off just so you can see what we have got. If I run this now, you will see I have two records for me, two records for Jim Kirk, and then one record for each of the other customers that have one order. This join shows all of this stuff and shows the orders where they match. Now, I did not bring any fields down from the orderT, and that is on purpose.
Watch this now. Right-click, Properties. We are going to set Unique Records to Yes. Now what is going to happen is: run it, and look at that. I have a unique list of customers that have placed an order. They show up in both tables, but I am getting a distinct row from the table that has fields down here.
Let me pause for a second because this is where a lot of people, including myself, get tripped up. You might be thinking, wait a minute. I have got an autonumber in the customerT table. That means every record in there is guaranteed to be unique. So if I have two customers named Richard Rost, each with a different customer ID, even with DISTINCTROW turned on, I should get two Richard Rosts in my query.
Well, yes, that is the case if you are just running the query on the customerT by itself. When you join in another table, the behavior changes. You are not crazy. This actually behaves differently. This tripped me up for a long time, so it is one of the reasons I honestly do not use DISTINCTROW that much.
What Access does is it looks at each record in the primary table (customerT), and it says, is this a unique record from that table based on the entire row of data from customerT? It does not care how many matching rows are in orderT yet. It does not care how many orders Richard has. It just looks at the customerT record and says, have I already shown this exact row from customerT? If the answer is yes, even if there are 10 orders over here, you only get one row in the results.
So it is kind of creating it like unique values, but it is really unique records, but it is not. I know it is confusing; trust me, I get it.
Now, if you have two separate Richard Rost records like we had earlier, you will see two different entries for the customerT because those are different records.
DISTINCTROW does not filter duplicates based on what shows up in the results. It filters based on the actual records in the primary table of the query. It is confusing. But primarily, the only time I ever use DISTINCTROW is when I am joining multiple tables and I want to get a unique list of these people that match to this table. It is like unique products that have been sold, that kind of thing.
Can you do this with an aggregate query? Yes, you can, and I have got videos on it. But this will run faster, especially if you are over a network.
When you are finished figuring all that out, if you want to learn more about SQL, and it is not all that hard, trust me, DISTINCT and DISTINCTROW is a tough topic to master, but I do cover it in more detail in my SQL Seminar Part 1.
Part 1 is all about the SQL language: SELECT and WHERE conditions, ORDER BY clauses, and all the stuff you are going to use in your database on a daily basis, like record sources, row sources, those kinds of things. Part 2 is all about action queries, append queries, update queries, delete queries, that kind of stuff. Part 3 is about modifying the structure of your database, so you can add fields and change indexes and all that kind of stuff.
Check it out. I will put a link to this down below if you are interested in learning more about SQL.
There you go, folks. That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Difference between DISTINCT and DISTINCTROW in Access
How to use the Unique Values property in queries
How to use the Unique Records property in queries
Creating a unique list of customers with DISTINCT
Comparison of aggregate Group By vs DISTINCT
Viewing SQL code for DISTINCT and DISTINCTROW
Performance differences between aggregate queries and DISTINCT
Understanding how DISTINCTROW works with autonumbers
Using DISTINCTROW when joining multiple tables
Getting a unique list of customers who have orders
How DISTINCTROW handles joined tables differently
When to use DISTINCT versus DISTINCTROW in practical scenarios
COMMERCIAL:
In today's video, we're discussing the difference between DISTINCT and DISTINCTROW in Microsoft Access queries, also known as unique values versus unique records. I'll show you what each one is for, how to use them, and when you should pick one over the other. We'll look at examples using customer and order tables so you can see exactly how these SQL keywords filter your data, and why they run faster than aggregate queries in some cases. If you've ever wondered which setting to use for your unique lists, you'll get a clear explanation here. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary difference between DISTINCT and DISTINCTROW in Access queries?
A. DISTINCT returns unique values from selected fields, while DISTINCTROW returns unique records based on all fields in the underlying table
B. DISTINCT counts the number of records, while DISTINCTROW sums values
C. DISTINCT is only used with aggregate queries, while DISTINCTROW is only used with action queries
D. DISTINCT ignores null values, while DISTINCTROW includes them
Q2. When should you consider using DISTINCTROW instead of DISTINCT?
A. When you want unique values of a single field
B. When you are joining multiple tables and need unique records from the primary table
C. When you want to run calculations on groups of records
D. When your table does not contain any autonumber fields
Q3. What happens if you use DISTINCTROW on a table that contains an autonumber field?
A. Almost every record will appear unique because the autonumber field will always differ
B. Only the first record will be returned
C. It will combine all duplicate records, even if autonumber is different
D. An error will be generated in the query
Q4. Why is DISTINCT generally faster than using an aggregate (Group By) query for getting unique values?
A. DISTINCT only checks for unique combinations of selected fields, without performing calculations
B. Aggregate queries do not work with large databases
C. DISTINCT uses less disk space than aggregate queries
D. Aggregate queries return more records by default
Q5. In the SQL statement SELECT DISTINCT FirstName, LastName FROM CustomerT, what will the result include?
A. One row for each unique combination of first and last name
B. All records, including duplicates
C. One row for each unique customer ID
D. Rows grouped by last name only
Q6. What must be true for two records to be considered duplicates when using DISTINCTROW?
A. Every field in both records must have identical values
B. The records must share the same first and last name
C. Only the primary key fields must be identical
D. Only non-null values must be the same
Q7. When does the behavior of DISTINCTROW become especially useful?
A. When joining multiple tables and you want one unique record from the primary table per match
B. When counting the total number of records in a table
C. When removing all records from a table
D. When sorting data by multiple fields
Q8. Which scenario best describes when an aggregate query would generally be easier or preferred over DISTINCT or DISTINCTROW?
A. When you need to perform calculations like SUM, COUNT, or AVERAGE grouped by values
B. When you want the fastest possible basic list of values
C. When you need to show every single row with duplicate entries
D. When you want to exclude records missing values
Q9. What is true about the UNIQUE VALUES property in the Access query property sheet?
A. It is the same as using SELECT DISTINCT in SQL
B. It disables the ability to run totals queries
C. It makes every record in the query unique by primary key
D. It prevents the use of JOINs in the query
Q10. If you select UNIQUE RECORDS (DISTINCTROW) while querying a join of CustomerT and OrderT, what happens?
A. Each unique customer appears only once regardless of how many orders they have
B. Each order is shown with the customer appearing multiple times
C. Only customers with no orders are shown
D. Only the first customer in the table is shown
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone will focus on understanding the difference between DISTINCT and DISTINCTROW, also known as unique values versus unique records, in Microsoft Access queries. I am going to explain what each of these options does, how to use them effectively, and when you might prefer one over the other.
This question comes up a lot, and it concerns the options Unique Values and Unique Records you see in the query property sheet. What do these mean, and how are they different? Knowing when to use each can make your database queries much more efficient and easier to work with.
To start, both DISTINCT and DISTINCTROW are somewhat advanced features. You usually do not need any programming for this, but you do need to be comfortable with SQL and understand how queries work beyond the basics. If you are just getting started or not too familiar with SQL, I strongly recommend reviewing an introduction to using SQL in Access. It took me many years of using Access before I really got comfortable with SQL myself, since most of my work was with the Query By Example grid and building forms or tables. Early on, my background was in BASIC programming, and I did not focus much on SQL until much later. A crash course or refresher will really help if you're new to writing SQL statements.
Additionally, you should be comfortable with aggregate queries, or Totals queries. Aggregate queries allow you to perform calculations like sum, count, or average, and you can group data by specific fields. While DISTINCT and DISTINCTROW tend to be faster and more efficient, aggregate queries are sometimes more straightforward for simple tasks. I have free tutorials about aggregates and SQL basics available on my site and YouTube channel, so if these topics are new, go review those and then come back for this discussion.
Let me show you these concepts in action using a sample database. I am working with a basic customer table. For clarity, I pared my data down to just a handful of customers, and I added a duplicate entry for myself to demonstrate how Access treats duplicates.
Suppose I want to create a query that lists only the unique customer names. Traditionally, I would create a query, add the customer table, and pull in the first and last name fields. If I run this, I will see two records for Richard Rost if I appear twice in the table. To show only unique names, I could turn on the Totals row (making it an aggregate query) and group by the name fields, which will result in just one listing for each distinct name. However, this approach is somewhat heavy for this simple task, because aggregate queries carry a bit of overhead.
An alternative way, and often a better one, is to use the Unique Values property in the query property sheet. Setting Unique Values to Yes will apply the DISTINCT keyword to your SQL statement. When you run the query now, Access will return only the unique combinations of first and last names, just like with the aggregate query, but it performs better, especially with large datasets.
What DISTINCT does under the hood is simple: it looks at the columns you bring into your query grid and removes duplicates so that only unique combinations are included. It is fast and efficient and is best used when you are not doing calculations like sum or count, but simply want to list out each unique entry once.
Now let us move on to Unique Records, also known as DISTINCTROW. This option is a bit less commonly used, but it does have its place, particularly when joining multiple tables together. If you set Unique Records to Yes, Access uses the DISTINCTROW keyword in your SQL.
Here's the key difference: DISTINCT (Unique Values) looks only at the fields displayed in your query output. DISTINCTROW (Unique Records), on the other hand, examines the entire underlying record from the main table—even fields you have not included in your output. This means that every column in the table must match for a record to be considered a duplicate and therefore removed.
Let me illustrate how this works. Imagine you have two identical customer names, but because each record has a unique autonumber CustomerID, they are treated as distinct records. When you use DISTINCTROW in a simple query on the customer table, you will still see both records, even though they look the same in the datasheet, because the autonumbers differ.
DISTINCTROW becomes especially useful when you are joining tables. Let's say you want a list of customers who have placed orders, and you have an Orders table with multiple entries per customer. If you join Customers and Orders, and run the query without DISTINCTROW, you will see one row per order, resulting in duplicates for customers with multiple orders. By turning on Unique Records, Access will collapse these so each customer appears just once in the results, regardless of how many matching orders there might be, as long as the customer record itself is unique.
Here is one point that confuses many people, myself included early on. DISTINCTROW uses the fields from the primary (main) table in your query to determine uniqueness, even when you join to another table. So if you have two customer records with the same name but different CustomerID values, both will appear as unique entries, even using DISTINCTROW, because they are indeed two distinct records at the database level.
In most everyday scenarios, you rarely have two identical records in a table, since there is nearly always an autonumber or unique ID field involved. That means DISTINCTROW's main use is when joining tables and wanting to filter based on the uniqueness of the entire record from the main table rather than just what appears on screen.
Can you achieve this with an aggregate query? Yes, you can. In fact, that is how I have traditionally handled it, and I have tutorials explaining how to do this. But DISTINCT and DISTINCTROW are often more efficient, especially with large datasets or when running queries across a network.
If you want to take your understanding of SQL even further, I cover these topics in more detail in my SQL Seminar. The first part covers the basics of SQL, working with SELECT statements, WHERE conditions, ORDER BY clauses, and the foundation you need for writing SQL in Access. The second part goes deeper into action queries—such as append, update, and delete—and the third part covers more advanced topics like modifying your database structure with SQL.
You will find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Difference between DISTINCT and DISTINCTROW in Access
How to use the Unique Values property in queries
How to use the Unique Records property in queries
Creating a unique list of customers with DISTINCT
Comparison of aggregate Group By vs DISTINCT
Viewing SQL code for DISTINCT and DISTINCTROW
Performance differences between aggregate queries and DISTINCT
Understanding how DISTINCTROW works with autonumbers
Using DISTINCTROW when joining multiple tables
Getting a unique list of customers who have orders
How DISTINCTROW handles joined tables differently
When to use DISTINCT versus DISTINCTROW in practical scenarios
|
