Sort Multiple
By Richard Rost
4 years ago
Sort By Multiple Fields in Tables, Queries, & Forms
In this Microsoft Access tutorial, I'm going to teach you how to sort by multiple fields (they're fields in Access, folks, not columns) in your tables, queries, and forms.
Recommended Courses
Reports
I don't cover sorting in reports in this video. That's because I've got a whole separate video on sorting & grouping in Access reports. Forgot to mention it in this video. Oops.
Links
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, microsoft access sort by two columns, Sorting records in a table by multiple fields, Sorting a Query Using Multiple Fields, Sorting by Multiple Fields in Microsoft Access, How to Sort Columns, Sort Multiple Fields in Access, Multiple sort problem in query, sort by two fields, sort by 2 fields
Subscribe to Sort Multiple
Get notifications when this page is updated
Intro In this video, I will show you how to sort by multiple fields in Microsoft Access tables, queries, and forms. We will cover sorting techniques for developers in tables, saving custom sorts in queries, and using those queries as the basis for forms. You will also learn how to create calculated fields for sorting, adjust form record sources, and set up simple buttons to help users access sorted data easily. This tutorial is packed with practical tips for managing and organizing your data more efficiently in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to sort by multiple fields in your Microsoft Access databases in the table, in a query, and directly in forms.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want, but what I'm going to show you works in pretty much any database.
The first sorting method I'm going to show you is sorting in your tables. Now, this is just for you, the developer, because we don't let our end users poke around in our tables. We hide this stuff. If you want to find out how to hide this stuff, go watch my simple security video. There is a link you'll find on my website or my YouTube channel. It's absolutely free. I'll put links to all the videos I'm going to show you down below.
To sort in your table by one field, that's pretty easy. You click on the field, right click, and go to sort A to Z or Z to A. There you go, that field is sorted. You can also use these little buttons up here.
To sort by multiple fields, just select them like this. Click on the header, select them like that, then hit the sort button. They'll be sorted left to right. So this will be sorted by first name, then last name. If you want to go last name, first name, just reverse the order of the fields. Click on the column, let go of the mouse, click again and drag it to the left. Now these are left as last name, first name. Select both of those and then sort, and now it's sorted last name, first name.
Let me say we've got James Kirk and let's add an Anna Kirk down here. Select both of these and then sort ascending. There you go: Kirk, Anna; Kirk, James.
Unfortunately, unlike Excel, you can't click and then control click to select multiple noncontiguous columns. You have to put the columns next to each other, but that's okay. Change these however you want. If you want to put the city up here, sort by city and then last name, sure. All right, city, last name, sort. There you go.
We don't want our end users poking around in our table, so I'm going to close this. Save changes? No.
Let's make a query and do the same thing. The beauty of a query is that you can save the sort, so if you have a complicated sort, you don't have to keep redoing the same work over and over again. It's good for someone who doesn't know Access. You can just make them a query, attach it to a button, and they're good to go.
Let's do something similar. Go to Create, Query Design. I'll bring in my customer table. You can bring in multiple tables if you want to. I'm going to bring in last name and then first name.
In a query, the fields will be sorted left to right, just like in the table. Here we go, here's the sort row. Go to sort ascending, sort ascending. There you go, you've created last name and then first name. Run it, and there it is.
If you want to do it the other way, just move the columns around. Click on that, let go of the mouse. When you have the arrow there, click and drag it to the left. Now it'll be sorted first name, last name.
If you want all of the fields in here, bring down the star. So now you've got all the fields in here. Watch what happens when you run this. Now you're getting duplicates, so you get this CustomerT.FirstName nonsense and then Field0, Field1. That's because you have two first names in here, so Access has to rename it.
All you do is just click this little Show box right there. That basically says, I want these columns in here, I want these fields in the query, but I don't need to see them twice. When you run it now, it's just sorted based on what you want.
Now you can save this query, Control S. You can call this CustomerSortedLFQ, or whatever you want to call it. Okay, customer sorted by last name, first name.
Now you can use this query as the basis for other forms and reports if you want to, or you can make a button for it. If you want to make a button so your average user can run that query, go to Form Design.
Now I'll use the Command Button Wizard because it's nice and easy. Come down to Miscellaneous and then Run Query. Pick which query you want to run, put some text down there like 'Sorted Customer List' or whatever you want to call it. Next, give it a meaningful name, such as 'CustomerSortButton' (for example, BTN).
There you go, there's your sorted customer list button. Normally, I teach people how to do this with VBA, but for today we'll keep it simple. Sorted customer list, there's your query. Now anybody can run that query. Just put it on your main menu.
Forms, on the other hand, if you go into a form, you can still use the same single field sorting technique that I showed you before. Click on the field, hit the sort button up here, or you can right click in here and go to sort, but you can only do it with one field at a time. So that's kind of limiting.
There is this thing called the Advanced Filter and Sort right here. I don't like it. You might as well build a query. I almost never use this. In fact, I really don't even teach it in many of my normal classes. I teach it in my Search Seminar because that's all the advanced stuff about searching, but I don't like that.
So I'm going to go to the form, just build a query that you want to have the form based on. If you have a custom sort that you do all the time, like last name and first name, you can simply make this form based on that query that we just made and add that field on here.
Watch this. I'm going to go to Form Design. The CustomerSortedLFQ has the data that I want sorted the way I want it. It is sorted by last name, first name. So I'm going to come in here, Design View. I'm going to add another field in here that I'm going to call LF. All right, so it's LF.
I'm going to zoom in so you can see this better. Shift F2, zoom in. LF is going to be last name and a space or even a comma space, that's fine, and first name. That's called string concatenation. That's going to put those two fields together and make one out of it. Ready? Watch this. Run it. Scroll all the way to the end here. Single field that's got the two things together that you want sorted.
Now we'll sort on this. Right click, Design View. I'm going to just get rid of these, and then we'll sort based on this guy. If you want, bring it to the front of the list. Save it and run it and look at that.
Now we'll use this to fill in our form. If you want to learn more about string concatenation, by the way, go watch this video.
Close this. Come back to your form here. Open up the form's properties and on the Data tab, change the record source to your query: CustomerSortedLFQ. Save that, close this, close this, open it back up again and look at it now. We're sorted based on that field. Last name first is the Antikirk and then James Kirk. You can even add that field on here if you want to. We'll just put it over here on the end. Go ahead, Add Existing Fields. There's my LF right there. Click, drag, drop it right there.
I'll delete the label, and we'll just slide this guy over here. I like to make these gray to indicate that you can't change that, because it's a calculated field so the user can't modify it. But you can still use it for searches and sorts. Go. There it is, right there.
So now if you have your form with a different sort method, and you want to sort it based on this field, right click, sort ascending. Now you're sorting based on that field.
You can take all the things you like to sort by, make little fields from them over here, and then you can right click on that field and sort based on that. Again, these are just shortcuts, little tricks and tips that I've developed over the last 30 or so years I've been working with Access. There are a lot more advanced methods and I cover them in my various classes.
I cover a lot with sorting and searching in my Access Beginner Level 1 class. This class is absolutely free. It's about four hours long. It's on my website, it's on my YouTube channel. Go watch it. I've got two whole lessons on sorting and searching.
In my Access Beginner Level 7 class, I cover a lot more with making a sorted list of customers, and I've got a bunch of other videos on sorting, like my Click to Sort, TechHelp video. This one's free. This is where I show you how to make labels over the columns in a form, and when you click on that label, it sorts on that field. That's pretty cool.
I've got a Search and Sort template that also has that click-to-sort label, but it also has search boxes across the top. You can type in what you're searching for, and it'll filter that list based on what you type in. So you can type in Kirk in here, and it'll sort that list and search it for the word Kirk.
Finally, I've got my big Search Seminar. This is the granddaddy that covers everything you ever wanted to know about searching and sorting and lots of different methods.
These things are called the Search Template and the Search Seminar because search is what most people search for. That's a nice search. Search is more popular of a search than sort, so I figured today's video is on sorting, but I cover sorting in my Search Seminar and my Search Template and all my search stuff. Searching and sorting kind of go hand in hand.
I'm going to throw one more little thing in here because I like to reward the people that wait until the end of the video and don't just leave when I start doing a little advertising. Gotta watch the advertising. This is like the post-credit roll at the end when you go watch a Marvel movie or a DC movie and they do something after the credits.
You made this little thing in here. We could put that in our continuous form too. I've got a customer list form right here. Right click, Design View. We're going to change this guy's properties; instead of CustomerT, we're going to go to that CustomerSortedLFQ. I'm just going to borrow this, copy, go over here, get rid of these two, and paste it in here. Like this and like that, and do a little bit of this. Spread these guys over.
Now I believe this guy is set so you can't edit the data in here anyway. Let's just check it out. Open this up. This one is set to Yes, but you can say Allow Additions - No, Allow Deletions - No, Allow Edits - No. That way the users can't mess with stuff on this form; they have to go to another form to do their stuff.
I'm just going to use the Format Painter. We'll do that and copy one of these labels, slide it over here and say Customer, and do a little bit of this. Save, close, close out, save changes - No, and then open it up and look at that. That's that field we just made and I just dropped it into a different form. Do you like it?
There you go. There's your Fast Tips video for today. I hope you learned something. Come check out my website and sign up for my free Access Beginner Level 1 class. It's free. It's like cheesy bread - it's free! Hope you learned something. We'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full length courses found on my website, not just for Access either. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a Sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the first place in Microsoft Access where Richard shows how to perform sorting by multiple fields?
A. In a table
B. In a report
C. In a macro
D. In a module
Q2. When sorting by multiple fields in an Access table, which direction are the fields sorted?
A. Right to left
B. Randomly
C. Left to right
D. Top to bottom
Q3. How does Richard recommend moving columns to change the sort order in a table?
A. By copying and pasting
B. By deleting and re-adding fields
C. By dragging column headers to reorder them
D. By changing field names
Q4. What limitation does Access tables have compared to Excel when selecting columns to sort by?
A. Only numeric fields can be sorted
B. Cannot select noncontiguous columns for sorting
C. Sorting is always descending
D. Cannot sort at all
Q5. What is the main advantage of using a query for sorting in Access?
A. Queries allow changing data while sorting
B. Queries can sort by more fields than tables
C. You can save the sort for reuse
D. Only queries can sort text fields
Q6. How do you specify a custom sort order in a query in Access?
A. By typing an ORDER BY clause in SQL view only
B. By arranging the fields in the desired order and setting Sort in the grid
C. By changing field types to numbers
D. By creating relationships between tables
Q7. If a field appears twice in a query, how can you avoid duplicate columns in the output?
A. Remove the field from the table
B. Uncheck the Show box for the duplicate field
C. Change the field's data type
D. Hide the column manually every time
Q8. What is the benefit of creating a button to run a sorted query for end users?
A. Users can modify the query structure
B. Users can access hidden tables
C. It provides easy access to sorted data without technical knowledge
D. Users can change field names on the fly
Q9. When working with forms, what is a major limitation with sorting from the user interface?
A. Can only sort on one field at a time
B. Cannot sort at all
C. Sorting changes the field names
D. Sort order is always random
Q10. What is the alternative to using the Advanced Filter and Sort option in forms, according to Richard?
A. Use macros to sort records
B. Base the form on a pre-sorted query
C. Export the form to Excel and sort there
D. Use report sorting instead
Q11. What does string concatenation in a query allow you to do for sorting purposes?
A. Merge two queries together
B. Combine multiple field values into one for custom sorting
C. Format dates differently
D. Protect fields from editing
Q12. Why might you make a calculated field like "LF" (last name, first name concatenated) in a query?
A. To simplify sorting and searching by a combined field
B. To hide user data
C. To restrict editing in the table
D. To increase database size
Q13. On a form, how does Richard suggest indicating that a field is calculated and not editable?
A. Set the background color to gray
B. Rename it to "Do Not Edit"
C. Move it to a hidden tab
D. Append an asterisk to its label
Q14. What is a recommended method if you often need a custom sort order for records on a form?
A. Rebuild the form every time
B. Base the form on a query with the desired sort
C. Add a macro to the table
D. Use report wizards each time
Q15. What feature does the Search and Sort template offer beyond just sorting?
A. Label printing
B. Built-in search boxes to filter records
C. Network multi-user support
D. Automatic data backups
Q16. Where does Richard recommend going to learn more about advanced sorting and searching techniques?
A. His Beginner Level 1 and Level 7 classes
B. Only the Microsoft Help files
C. Free templates from other websites
D. The Access Options menu
Q17. What member benefit is exclusive to Gold members according to the video?
A. Access to all beginner classes
B. Download all sample databases from TechHelp videos and access the Code Vault
C. Direct one-on-one coaching
D. Free Microsoft certification exam
Q18. What does becoming a Diamond Sponsor allow you to do?
A. Moderate Richard's forums
B. Have your name or company listed on sponsor pages and get a shout out
C. Get private lessons with Richard
D. Advertise in the AccessLearningZone newsletter
Q19. According to Richard, what is the relationship between searching and sorting in Access?
A. Searching and sorting are separate actions with no overlap
B. Sorting is more commonly performed than searching
C. They go hand in hand and are often used together
D. Only searching is important for users
Answers: 1-A; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-A; 10-B; 11-B; 12-A; 13-A; 14-B; 15-B; 16-A; 17-B; 18-B; 19-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 is all about how to sort by multiple fields in your Microsoft Access database. I'll cover three main places where you might want to sort your data: in tables, in queries, and in forms.
To start with, let me say that while you, the developer, may want to sort data directly in your tables, it's not something you want your end users doing. If you're interested in keeping users out of your tables and want to learn more about database security, make sure to check out my Simple Security video. It is available for free on my website and YouTube channel.
Sorting by a single field in a table is pretty straightforward. You can pick the field, right-click, and choose to sort in ascending or descending order. The ribbon buttons at the top give you the same options. If you want to sort by more than one field, make sure the columns you want to sort by are next to each other in the table. Select all the columns in the order you want the sort to take place (left to right). For example, if you pick first name and last name, it will sort by first name, then last name. If you want last name first, just move that column to the left, select both, and sort again.
It's important to mention that you can't select non-adjacent columns for sorting, as you might in Excel. The columns you want to sort by need to be together. You can move them wherever you like to get the sort order you're after. Once you're finished, remember to close the table without saving if you don't want to keep those changes visible.
Moving on to queries, this is often a better place to handle sorting, especially if you want to save your sort logic to use again later or share with others. In Query Design, you bring in the table (or tables) you're interested in, add the fields you want, and then set their sort order using the Sort row in the Design grid. Just as in tables, the fields are sorted left to right. If you want last name first, then first name, arrange your columns that way and apply the appropriate sort options.
If you want to display all the fields from your table while sorting just by a few of them, you can add all fields to the query using the wildcard and then uncheck the Show box for the additional fields you are using solely for sorting. This way, you avoid duplicate columns in your results.
Saving your query gives you a way to reuse your defined sort order. For example, you could name it something like CustomerSortedLFQ for a query sorted by last and then first name. Once it's saved, you can use this as the basis for other forms and reports or set up a button that runs the query for your users using the Command Button Wizard. Even users unfamiliar with Access can use this button to see the sorted list.
When it comes to forms, the built-in sorting options are a bit more limited. You can click a field and sort it, but only one field at a time. There is an Advanced Filter and Sort feature, but I generally don't recommend it, as designing a query usually gets better and more flexible results. If you have a certain sort you use all the time on a form (say, last name then first name), build a query with this custom sort and have your form's Record Source point to that query. You could also add a calculated field in the query that combines last and first name, and use that for sorting inside the form as well.
For example, you might create a field that joins last and first names for display and sorting purposes, using string concatenation. Add this calculated field to the query, and then include it on your form. I like to format these calculated controls with a gray background to indicate they are read-only. Users can sort by this field with a simple right-click.
You can use this method for continuous or datasheet forms as well. Just update the form's record source to use your sorted query, add the calculated field to the form, and arrange it as you like. If you want to prevent edits on a display-only form, set Allow Additions, Deletions, and Edits to No in the form properties.
I have several resources to help you master sorting and searching in Access. In my free Access Beginner Level 1 class, available on my website and YouTube, you'll find two detailed lessons covering sorting and searching. For more advanced sorting options, including creating sorted lists and form-based column sorting (where you can click labels to sort), take a look at my Click to Sort TechHelp video and my Search and Sort template. My Search Seminar goes over even more techniques, combining searching and sorting in many ways. Searching and sorting in Access often go together, so these resources are designed to help you with both.
As a final tip, the tricks you use in Datasheet forms can also be used in continuous forms to display combined and sorted fields in creative ways. This lets you present sorted data to your users in a friendly and flexible manner.
If you're interested in supporting my channel and unlocking more content, you can become a member with various levels, each giving you access to additional TechHelp extended cut videos, free classes, downloadable sample databases, and more. The videos will always be free for everyone to watch, so as long as you keep tuning in, I'll keep producing new content.
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 Sorting by multiple fields in a table
Selecting adjacent columns for multi-field sorting
Reordering columns to change sort order
Sorting by city and last name in a table
Creating a query to sort by multiple fields
Using the sort row in query design
Reordering columns in a query for different sorting
Including all fields using the asterisk in queries
Hiding duplicate columns by adjusting the Show setting in queries
Saving a query with multi-field sorting
Using a saved query as a data source for forms and reports
Creating a button to run a sorted query
Sorting in forms using single field sort
Limitations of sorting multiple fields directly in forms
Using Advanced Filter and Sort option in forms
Building a form based on a pre-sorted query
Adding a calculated (concatenated) field for sorting
String concatenation to combine fields for sorting
Changing a form's record source to a sorted query
Adding a calculated sort field to a form
Formatting calculated fields to be read-only in forms
Copying and pasting sort fields into continuous forms
Adjusting form properties to restrict edits in continuous forms
Using a non-editable summary field on continuous forms |
