Initial Sort Order
By Richard Rost
2 years ago
Setting an Initial Sort Order for Records in a Table
In this Microsoft Access tutorial, I'm going to teach you how to number records sequentially, starting at zero and counting up, so that you can specify an initial sort order which you can then later use to manually reorder your records. We will use an update query and the DCount function.
Members
Members will learn a much more efficient and faster method of numbering records using VBA code and a recordset loop.
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
Links
Recommended Courses
Keywords
TechHelp Access, initial sort order, update query, DCount function, renumber customer ID, Access sort customization, sequential record numbering, Access query design, custom sort field, Access table renumbering
Intro In this video, I will show you how to set up an initial custom sort order for your records in any table using Microsoft Access. You'll learn how to add a sort order field, use an update query with the DCount function to automatically number your records, and understand when and why this method is useful, especially if you have a large number of records. We'll talk about some of the limitations to watch out for and discuss how to use this sort order to control the display of your data in list boxes or forms.Transcript In today's video, I'm going to show you how to seed your initial sort order for the records in your table - any table - in Microsoft Access. What does that mean? You want to add a custom sort order field so you can order these guys in any way that you want to, by hand. You have to start that numbering somewhere instead of sitting here typing in 0, 1, 2, 3, 4, 5, 6, and so on. I'm going to show you how to do it with a query.
Why are we doing this? In the last couple of videos earlier this week, I showed you how to make a form where you can make a couple of buttons to move items up and down in a list. This involves having a sort order field stored in that table. If you only have four items, it's no big deal - just type in 0, 1, 2, 3. You'll get your initial sorting and then you click the up and down buttons to move the items up and down.
If you want to learn more about this, go watch this video here. Start with part one; there's a link for part one. In part one, we just use a value list list box. These records are not actually bound to a table - they're right in the list box. That's the easy one. In part two, we move to a list that's bound to a table, so these are actually records in a table. In this video, I show you how to set up the sort order and we just type in, and I tell you in the video, if you have thousands of records, you don't want to sit here and type them all in.
You have an existing list of, let's say, 6,000 customers. You don't want to have to type them all in. So, how can we have a query fill those sequential numbers in for us? That's what we're going to learn how to do in today's class.
Go watch these list box move item videos if you want. I consider this an expert level video, which means we're not going to need any VBA programming. We can do this without VBA code, although later on in the extended cut for the members, I'm going to show you a better way to do this with some VBA code. Generally, you can do this with just queries and functions. I call it an expert video because it's beyond the basics, but you don't have to quite know how to program. You will need to know, however, what update queries are. If you've never used an update query before, go watch this video.
You need to know what DCount is - the DCount function. We use it to count records based on whatever criteria we specify. If you've never used DCount before, go watch this video first. It's a relative of the lookup, but go watch this video if you need to know how to use DCount. Go watch it, then come on back.
Here I am in the TechHelp free template. This is a free database. You can grab a copy from my website if you want to. In here, I've got a customer list. I've got a bunch of customers - 33 of them. Let's pretend I have 33,000. I want to add a custom sort order, or I want to be able to change the order manually in which these guys are sorted. Not by ID, not by name, not by email, but I want a manual sort order. That's what the previous couple of videos were talking about.
Let's go to Design View here and let's add a sort order field to our table: SortOrder. That will be a number. Default value is zero. That's fine. Save it. Close it. If you open it up and take a look at it, you have a blank field there. Bring it to the front of the table - click and drag, slide it over here right after the Customer ID. Blank, that's fine. Save it. Close it.
Let's create a query. Go to Create, Query Design, bring in your customer table. Let's take a look at the Customer ID. Bring in that SortOrder, which is blank. Let's make a new field over here. We'll call it x. Let me close this - we don't need that. I'm going to zoom in here - Shift F2 so you can see this better. My zoom box is gigantic. Let me resize this. There we go.
Make a new field called x. Now, here's where we're going to use our DCount function. x is going to be DCount. I want to count how many records have a Customer ID lower than the current record. That should tell me its position in the pecking order.
So, DCount: what's the field? Count CustomerID, comma. From what table? CustomerT. That's my criteria, where the CustomerID is less than and CustomerID. That's going to take the current CustomerID in the record that I'm on - 1, 2, 3, whatever - put that inside here.
My complete code is going to look like this for that line. It's going to be: give me how many CustomerIDs from the customer table are less than CustomerID - less than three, in which case you should get a two. I'm going to put that CustomerID outside the string, so it looks like that. This gets evaluated first. Then, this whole thing gets evaluated and that gets assigned to x.
If I run this now, there you go. There's your x value. How many CustomerIDs are less than one? None of them, zero. How many CustomerIDs are less than two? Just one. And so on. There's your initial sort order right there.
Now, all I have to do is take that value and stick it in SortOrder. How do we update the value of a field? We'll do that with an update query. We're just going to change this to an update query. We don't need CustomerID for that. Get rid of that. I'm going to take this calculation right here - everything from the DCount afterwards. We're just going to cut that out and put it in the clipboard (Ctrl-X). Get rid of that - we don't need it.
Now, we're going to switch this to an update query. For the Update To field, slide this over, update to, paste in that value right there. In other words, we're saying: run this, and for each SortOrder, set it equal to whatever that value comes out to be.
Save this as MyRenumberCustomerQ. A little update query right there, and go ahead and run it. Nothing appears to happen. I have my warning messages turned off. You might get a warning message that says you're about to update 33 records or whatever.
Now we can close this and take a look at our customer table. Look at that. It did it. Now you have your custom sort order starting with zero. You want to start with one? You can start with one if you want to, but I used zero in the other videos so I figured I'd stay consistent.
Now you can use this to feed a list box like we did in the previous videos. Keep in mind, this method is notoriously slow if you've got lots and lots of records. Update queries, and queries in general, I try to avoid putting domain functions in here - DCount, DLookup, DMax, DMin, any kind of aggregate functions - because they will run slow. This has to evaluate for each record in this table; it's got to count all the other records. In this case, it's only 33 times, but if you've got 100,000 records in here, this is going to take a while to run.
If you're only initially seeding your values and you only have to do it one time, don't worry about it. Just do it once, let it be done, that's over. But if this is something you have to do on a regular basis, you might want a more efficient method of doing this, of renumbering your records. That's what I'm going to cover in the extended cut.
In the extended cut for the members, we're going to use a record set loop in some VBA with a little counter. We're just going to run through the records real fast and auto-number them. With this method, it's much easier to sort these differently. If you want to initially sort these based on last name, first name, email address, date they were added, or whatever, it's easier to add a sort with this method.
You have to be careful sorting the records if you're just doing it with the query like I just showed you, because if you've got duplicates in there, you're going to mess up your count. If you have two William Rikers, for example, their number might be the same. You've got to watch out for that. In the method that I'm going to show in the extended cut, you won't have that problem.
Silver members and up get access to all of my extended cut videos, and Gold members can download these databases and stuff. There are lots of reasons to join today.
Also, a couple of other things: What happens when you add a new customer and you want to keep these numbers going? I've got another video that shows you how to update that record number so the last item that gets added automatically puts the next record number in. Go watch this video.
I've also got a video on sequential numbering which you can use. This is good for things like check numbers, where you might start it out at 1001, and you want to make sure that the next one is just one more than the largest one in the table. So it doesn't necessarily start from zero or start from one. It starts from whatever the largest one is. This one uses DMax, a slightly different method.
There are lots of different ways to do this stuff. I just show you the different Legos. You can put them together however you want. If you like this stuff, if you like learning, come check out my lessons. I have tons of free beginner lessons on my website. I have expert, advanced, developer lessons. You name it - any skill level - I have lessons for you. Check it out. You'll find links down below.
That's going to do it. There is your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.Quiz Q1. What is the main goal of seeding an initial sort order in an Access table as described in the video?
A. To assign a manual ordering to records for custom display
B. To sort records only by their ID field
C. To automatically remove duplicates from the table
D. To filter records based on a certain criteria
Q2. Why would you want to use a query to assign initial sort order instead of typing values by hand?
A. It is much faster for large numbers of records
B. It prevents errors in record data
C. It ensures records are always sorted by name
D. It creates primary keys automatically
Q3. Which function is used in the video to count the number of records with a lower CustomerID than the current record?
A. DCount
B. Sum
C. DMax
D. Avg
Q4. What type of query is used to update the SortOrder field in the table?
A. Update query
B. Append query
C. Crosstab query
D. Make-table query
Q5. What is a potential drawback of using DCount in a query to renumber a very large table?
A. It can be very slow for large tables
B. It can create duplicate records
C. It deletes records with missing values
D. It changes the data type of the field
Q6. In order to use the method shown in the video, what must you add to your table first?
A. A new numeric field for sort order
B. An additional primary key
C. A calculated field with a text data type
D. A secondary index on customer name
Q7. When constructing the query to seed sort order, which information is counted for each record?
A. The number of CustomerIDs lower than the current one
B. The number of empty fields in the record
C. The total number of records in the table
D. The number of records with a higher CustomerID
Q8. Which scenario best justifies using the query-based approach for seeding sort order?
A. When working with a very large dataset to avoid manual entry
B. When working with only two or three records
C. When sorting is based solely on alphabetic order
D. When there are no numeric fields in the table
Q9. What should you be cautious about when using this query-based DCount method if there are duplicate values?
A. Duplicate records can result in identical sort order numbers
B. Duplicate records will be automatically removed
C. DCount will ignore all duplicate records
D. Duplicate records will get skipped in numbering
Q10. Why might you prefer using a VBA-based method for regular renumbering of very large tables?
A. It is significantly faster than domain functions in queries
B. It always starts numbering at zero
C. It does not require any queries at all
D. It automatically sorts by date added
Q11. What is the main purpose of the SortOrder field once populated?
A. To control and customize the display order of records in forms or lists
B. To ensure no duplicate values exist in the table
C. To link the table to another table
D. To store the creation date of each record
Q12. If you want new records to be assigned the next available sort order automatically, which function could help?
A. DMax
B. DMin
C. DSum
D. DLookup
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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 explores how to set up an initial custom sort order for your records in a Microsoft Access table. Many times, you will want to control the order in which records appear, independently from built-in sorting options like IDs or alphabetically by name. By adding and seeding a custom sort field, you can manually organize your records as you prefer.
The reason for this approach stems from previous lessons where I demonstrated how to create a form with buttons to move items up and down in a list. This process depends on having a sort order field in your table. If your table only contains a few items, you can simply enter 0, 1, 2, 3, and so on by hand. However, this method quickly becomes impractical if your table has hundreds or thousands of entries.
If you are interested in learning more about the concept of moving items in a list, I recommend starting with my earlier tutorials. In those, I began with a simple value list in a list box and then advanced to handling records bound to an actual table, where you need a dedicated sort order field. In those videos, I explain that manually entering sort order values is not feasible for large data sets such as a customer table with thousands of entries.
To automate the process of seeding the sort order, we will use a query. This tutorial does not require any VBA programming. We will handle things strictly with queries and built-in functions, but you should be familiar with update queries and especially the DCount function, which counts records matching specific criteria. If these topics are new to you, I suggest reviewing my tutorials on update queries and DCount to get caught up.
In my demonstration, I work with a free TechHelp database template, which you can find on my website. This demo table has a collection of customers, but you should imagine a much larger set for this real-world scenario. Our goal is to add a custom numeric SortOrder field, allowing manual ordering instead of sorting by standard fields.
First, we add the SortOrder field to the table in Design View, making sure it is a number type, and assign it a default value, usually zero. Once saved, the field will be blank in your records, ready to be populated.
Next, we create a query to calculate the initial sort order. I include the Customer ID and the blank SortOrder field, then introduce a calculated field called x. This field employs the DCount function, which counts how many Customer IDs are less than the current record's ID. The result for each record will be its initial sort order position in the list.
Running the query will display the x values, confirming that each record is assigned a sequential number based on its position. The next step is to use this calculation in an update query so that each SortOrder field value is set to its corresponding x value. Saving and running this update query will sequentially number all your records in the SortOrder field, starting from zero or one, depending on your preference.
This seeded SortOrder can now control the order of items in a list box or other form elements, allowing users to manually rearrange records as needed. However, keep in mind that this method becomes inefficient for very large datasets because the calculation relies on domain functions like DCount, which Access must evaluate for every record. For an initial, one-time numbering of your records, this approach is fine, but if you find yourself needing to resequence records regularly, you will want a more efficient solution.
In today's Extended Cut, I go a step further and demonstrate how to accomplish the same task using VBA. By looping through a recordset and incrementing a counter, you can renumber large sets of records more quickly and with greater flexibility, including the ability to initially sort based on any field you choose. This VBA method also avoids issues with duplicate records that can appear when using only queries.
For members, the Extended Cut includes a full walkthrough of automating this process using VBA. Silver members and above have access to all extended cut videos, and Gold members can also download example databases to use or study.
If you need to update the numbering when adding new records, I have another tutorial that shows how to assign the next available sort number automatically, so your new entry gets the correct sequence. Additionally, I cover sequential numbering for check numbers, starting at any arbitrary value, by using the DMax function.
There are many ways to customize how your records are ordered and numbered in Access. I provide you with the building blocks so you can assemble a solution that fits your needs.
If you are interested in more lessons, I offer a wide range of options, from free beginner classes to advanced and developer-level courses. You will find everything you need for any skill level on my website, with links provided below.
To watch a complete video tutorial with step-by-step instructions about everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Adding a sort order field to an Access table
Positioning the sort order field after Customer ID
Using DCount to calculate sequential numbers
Creating a query to generate initial sort order values
Building a DCount expression to determine record position
Running a select query to preview calculated sort order
Converting the select query to an update query
Using an update query to populate the SortOrder field
Saving and running the update query to assign sort order
Viewing the updated sort order in the table |
