Top X
By Richard Rost
4 years ago
Select Top X, Top X% Records with Numbered Rows
In this Microsoft Access tutorial, I will show you how to create a Top X query to show the top 10, top 50, top 20%, etc. of records in your table. I'll also show you how to number the rows using the DCount function.
Pre-Requisites
Links
Row Count
DCount("CreditLimit","CustomerT","CreditLimit>" & [CreditLimit])+1
Recommended Courses
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Find the records with the top or bottom values, SELECT TOP, Top Value Query, top n records, bottom n records, top percent, top percentage, top percentile, numbered rows, counter, count rows
Subscribe to Top X
Get notifications when this page is updated
Intro In this video, I will show you how to create a Top X Query in Microsoft Access to display the highest or lowest set of records, such as the top 5 or bottom 10 customers by credit limit. I will walk you through sorting results, choosing your top count or percentage, and customizing your queries. As a bonus, I will also demonstrate how to number the rows in your query results using the DCount function for small data sets.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to do a Top X Query. This is where you can say you want the top 5 records, top 10 records, top 20 records, or the bottom 5, bottom 10, whatever. As a bonus, after I show you that, I am going to show you how to number the rows right in your query.
Here I am in my TechHelp Free template. This is a free database. You can grab a copy off my website if you want to. I will put links down below.
In my database, I have got a customer table right here, and my customers have lots of different fields we could use. I like credit limit for this example. I want to see the top, let's say, five customers who have the biggest credit limits.
So let's go and make a query. Create, Query Design. I am going to bring in the customer table, and we will close that down.
If you have never made a query before, go watch my Access Beginner One Class. It is free. It is on my website. It is on my YouTube channel. I will put links to all this stuff down below.
So let's bring in first name and last name, and let's go find Credit Limit. Where are you? Right there.
If I run the query now, there are no particular orders. So let's go ahead and sort them. I want the largest ones first. I am going to go to the sort and go descending. Now I run it. Now they are sorted from top to bottom, from biggest to smallest.
One more trip back in Query Design. Right there, you see Return, and it says All. Right now, by default, you get all the records. Drop that box down. You have 5, 25, 100. Let's pick 5, and now run it. Look at that. You have the top 5 results.
Pretty cool. Let's change it to 25. Here are the top 25 records. Very nice.
What if I want 10? How do I get there? There is no 10 in there. Well, we will just come right in here and type in 10. Be careful, because if it goes to 100, press delete, and it will give you 10. Tab or enter, and then run it. There is your top 10.
Want the bottom 10? Just change your sort. Change that to ascending. Now let's go back to descending. Now I want the big guys. I want the top, the big ballers up here, like me.
You can also use percentages. Notice there are some percentages in here, 5, 25. You could say give me the top 15 percent if you want, and then run it. There you go. There is the top 15 percent of your records.
Someone says they want to see the top 13 percent of sales reps with the highest performance. Now let's go back to top 10. Not 100. I do that all the time, and I mention that in the video because literally whenever I do this, I want a top 10, and it always gives me 100. So I have got to hit delete.
There are the top 10 records.
So far, that was all easy stuff. How do we number these guys? I want to put a number there. To do that, we are going to use a little function, one of my favorites, called DCount.
If you have never used DCount before, go watch this video first. One of my free videos on my websites, on my YouTube channel, I will put a link down below and you can go watch it.
DCount basically says go out to a table, any table, and based on the criteria that I give you, give me a count of the number of records that meet that criteria.
So go watch this and then come back.
How can I use DCount to give me a count over here of the number of records that I am on? This is record four, this is record five, and so on.
If you look at this value here, if I look in the table, I have got the credit limit sorted, so the biggest one is on top, the second biggest one is next. All I have to do is say, I want to count the number of records that have a credit limit larger than that one. That should return a zero. Add one, now I am on record one.
Let's take a look at the next one. I want to count the number of records that have a credit limit greater than that one. That should return a one. Add one to it. Now I am on record two. See what I am doing here?
How about this one? I want to DCount the number of records that have a credit limit greater than this one. There are two of them. Add one, I am on record three. See how this works?
I am going to give you a bit of a warning. Only do this with very small sets of records. You do not want to use the domain functions like DCount, DLookup, and all those in a very, very big query. If you have thousands of records, this will run slowly.
But for something like this, where we want top 10, top 20, 30, whatever, even 100 records, it is not going to be super slow. If you have 10,000 records in here, try to avoid those D functions like DCount. For this, it is perfectly simple.
I am going to put it right here and then we will move it over to the beginning. I am going to zoom in so you can see, Shift-F2.
We will call this "MyNumber" and "YouNumber." What number am I on? That is going to be DCount.
You want to count the field that you are counting because you do not want null values getting in here. You can do a count of the star like I show in the other video, but you do not want that because you do not want to count all the records. You want to count just the records with credit limits.
So I am going to count up Credit Limit from the Customer table where the credit limit is greater than whatever the current credit limit is. Plus one. I will make this a little bit bigger, so it is easier to read.
DCount the credit limit field from the Customer table where the credit limit is greater than whatever the credit limit of the current record is, and then add one to it. Then we will run it.
There is your count. Look at that. If you flip this, if you are doing smallest to biggest, you just want to change that sign in here. Change that from greater than to less than.
We will take this and move it over here. Do something like that. Run it. There you go. There is your count.
This will work fine too if you have ties in here. For example, if you have, let's say, two people with, well, let's do three people in here with a $4,000 credit limit. You might have to rerun the query to recalculate. There you go. You have one, two, three, four, four. They tied for fourth place, then it goes right to seven. So that works fine.
Did you like learning this kind of stuff with me? In my Access Beginner Level 5 class, I cover a lot more with query stuff, including those Top X values. We spend more time on those. In Access Expert Level 29, expert stuff, a little more advanced, I cover all those domain functions: DLookup, DSum, DCount, DAvg, DMax, DMin, DFirst, DLast. All kinds of D stuff in there. Those are all really cool functions.
That, my friends, is your Fast Tips video for today. I hope you learned something. I will see you next time.Quiz Q1. What is the main purpose of a Top X Query in Microsoft Access?
A. To display the top or bottom N records based on a specific field's value
B. To combine data from multiple tables automatically
C. To generate complex reports using macros
D. To convert text data into numbers
Q2. In the video example, which field was used to demonstrate the Top X Query?
A. Last Name
B. Sales Performance
C. Credit Limit
D. Customer Address
Q3. How do you sort records from largest to smallest in an Access query?
A. Set the sort order to Ascending
B. Set the sort order to Descending
C. Use the Aggregate Totals option
D. Group by value
Q4. If the value you want (such as top 10) does not appear in the Return drop-down for queries, what should you do?
A. Choose the closest available value
B. Manually type the desired number into the Return box
C. Use a filter instead
D. Change the query type
Q5. How can you retrieve the bottom N records in a Top X Query?
A. Sort by descending and select N records
B. Delete the top records until N are left
C. Change the sort order to ascending and set the Return value to N
D. Use DCount to filter records
Q6. What percentage options are available by default in the Return drop-down?
A. Only 5% and 10%
B. Only 25% and 50%
C. 5%, 25%, and 100%
D. None, you must type percentages manually
Q7. What function is demonstrated to add a row number to each record in your query?
A. DLookup
B. DSum
C. DCount
D. DAvg
Q8. What is a key limitation of using domain functions like DCount in queries?
A. They cannot handle numeric data
B. They work only with one table at a time
C. They are slow when used with large datasets
D. They only work on filtered queries
Q9. In the example for numbering records, why do you add one to the result of DCount?
A. To skip the first record
B. To ensure ties are counted twice
C. Because DCount gives one less than the actual position
D. To count null values as well
Q10. What happens when there are ties (multiple records with the same credit limit) in the query?
A. The numbering skips tied values
B. The query produces errors
C. Tied records get the same rank, followed by the next correct number
D. Only one of the tied records appears
Answers: 1-A; 2-C; 3-B; 4-B; 5-C; 6-C; 7-C; 8-C; 9-C; 10-C
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 video from Access Learning Zone covers how to create a Top X Query in Microsoft Access. This feature allows you to specify that you want to see, for example, the top 5, top 10, or top 20 records based on a certain field, such as the largest or smallest values. You can also use it to display the bottom records if needed. In addition, I will show you a technique to number the rows directly within your query.
For demonstration purposes, I am using the TechHelp free template. If you want to follow along with the same database, you can download it from my website.
In my sample database, I have a customer table with various fields, one of which is Credit Limit. Let us say our goal is to view the top five customers with the highest credit limits.
To start, I head into Query Design, add the customer table, and include the First Name, Last Name, and Credit Limit fields in the query. If you are not familiar with creating queries, I recommend watching my Access Beginner One class, which is available for free on my website and YouTube channel.
After setting up the query and adding the relevant fields, running it at first will not display the records in any particular order. To sort the results from highest to lowest Credit Limit, it is necessary to set the sort option for the Credit Limit column to Descending. Running the query now displays customers starting from the largest credit limit down to the smallest.
A key feature in query design is the Return property, which defaults to 'All' records. By selecting 5 from the dropdown, you can limit the results to the top five records. Adjusting this to 25 gives you the top 25 records. If you want a number not offered in the list, such as 10, simply type it in manually. Make sure to clear any existing value if needed. Then, rerunning the query provides the desired number of records.
You also have the option to see the lowest values, for example, the bottom 10, by changing the sort order to Ascending. Switching back to Descending will show the largest values again.
Access allows filtering by percentage as well. You can select options like the top 5 percent or 25 percent. If you want, for example, the top 15 percent, just type that value in, and Access will display that segment of your records.
Many users frequently need to see results such as the top 13 percent of sales reps by performance or the top 10 sales by value. The process remains consistent: select the appropriate return value and sorting as needed.
The next step is to add row numbers to your results. To do this, you can use the DCount function. If you are unfamiliar with DCount, I suggest you watch my free video where I explain how DCount works in detail. Essentially, DCount counts the number of records in a table that meet specific criteria.
Applying this to our case, to generate a row number for each record, use DCount to count how many records have a higher Credit Limit than the current one. For the top record, the count is zero, but by adding one, you arrive at one for the first row, two for the second, and so on. For each record, this approach tells you its position in the sorted list.
Be aware, however, that domain functions like DCount can slow down performance if you are working with large datasets, such as tens of thousands of records. For smaller sets, like top 10 or top 100, it works just fine.
In the query, you can create a field for numbering the position, for instance "MyNumber," calculated by counting Credit Limits in the customer table that are higher than the current record's Credit Limit, and then adding one. This works smoothly when the records are sorted in descending order. For ascending order (lowest to highest), adjust the comparison operator accordingly.
It is also worth noting that this method handles ties appropriately. If several records share the same Credit Limit, they will have the same row number, and the numbering will skip ahead to the next correct value.
If you enjoy learning these kinds of techniques, I discuss Top X queries in greater depth in my Access Beginner Level 5 class. For a comprehensive look at domain functions such as DLookup, DSum, DCount, DAvg, DMax, DMin, DFirst, and DLast, I cover these in Access Expert Level 29. These topics offer a lot of powerful tools you can use to analyze and summarize data in your databases.
You can 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 Creating a Top X Query in Access
Sorting query results in descending or ascending order
Using the Return dropdown to limit query results
Manually entering a custom number in the Return box
Displaying top or bottom X records
Using percentages to limit query results
Numbering rows in a query result
Using DCount to create a row number field
Adapting DCount logic for ascending or descending sorts
Handling ties when numbering rows in query results
Performance considerations when using DCount in large queries |
