|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Custom Sort Order By Richard Rost 
6 years ago
Use a Custom Sort Order for your Forms and Reports In this Microsoft Access tutorial, I will teach you how to specify a custom sort order that you can use to display records however you like in your tables, queries, forms, and reports. Joyce asks, "I have a list of companies that I want to put into my reports, but I don’t wanted them listed alphabetically. I need to specify how they’re sorted. How do I do this?" MembersI'll show you how to have Access automatically determine what the next item in the sort order will be and fill it for you. I'll also address multi-user concerns (what if you both add a new record at the same time). 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! Links
IntroIn this video, I will show you how to create a custom sort order in Microsoft Access so you can sort records in your tables, queries, and reports using any order you define, instead of the default alphabetical or numeric sequence. We will cover how to add a sort order field, set custom values, and use that field to control the sort order in your queries and reports. This technique is perfect for situations where you need full control over how your data is displayed, like Joyce's example where company names need a specific order.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's lesson, I am going to show you how to create a custom sort order. You can sort the items in any table or query based on a custom order that you define. Today's question comes from Joyce. She says, I have a list of companies that I want to put into my reports, but I do not want them listed alphabetically. I need to specify how they are sorted. How do I do this? Joyce, this is something I actually get asked a lot. The answer is simple. It is as simple as adding another field to your table. Let's say I got a list of customers here. Here is my customer list. I want to sort them in a particular order, not necessarily last name, not necessarily first name. Nothing in the table right now indicates how I want them sorted. It is something I just want to pick. All I have to do is add a sort order field to this table and then type those numbers in myself. Let's do it with a new table. Create table design. Let's call this our company table. This is our company ID, auto number, the company name, and then a sort order. Do not just put the word "order" in there. Order is a reserved word; Access does special things to it. We are going to make this a number. Now the default value is zero. That is okay. New items will get a zero. You will have to put the sort order in yourself, but that's okay. Let's save this as my company table. Primary key, yes, there we go. Close it down, open it back up again. Let's put some companies in here. Let's put in Microsoft, IBM, Apple, Tesla, Ford, and whatever else you want in there. Computer Learning. Now I want to specify what the sort order is. That is not necessarily going to be alphanumeric. Usually, things that you sort by are a number of sales or the date they became a customer. This is just something that you want to pick off the top of your head. Of course, Computer Learning Zone goes first. Then let's say Tesla, then Ford, then Microsoft, then Apple, then IBM. You could repeat these if you want to. If you have two number fives, then that is okay. You just can't predict which way they will be sorted unless you do a double sort. You can just sort by sort order, then by company name or whatever. If you want to make sure that this list is not duplicated, you can index this list. Click design view. You can index this right there. Make it yes, no duplicates. That way, you can't have two item fours, for example, with the same sort order. Personally, I do not think that's a big deal. If later on you decide you want more granularity and you do not want to have to re-number the whole thing, you can simply change this to a double or a decimal value so you can go 5.1. There are all kinds of tricks you can play, but this gives you the idea of how to do a basic custom sort. How do you sort this stuff? If you make a report, there is the option in there to sort based on a particular value. But you can also do it with a query. Create query design, bring in my company table. I want to see the company ID, the company name, and the sort order. Then I am going to sort based on that field. When I run it, now you can see they are sorted in my sort order. I can now base other forms, queries, and reports off of this. So we will call this company sorted queue. It is like I got my own custom sort order. I can make mailing labels off of this. I can do reporting off of this, all kinds of stuff. The only difficult thing now is putting in the next sort order item. If you add another company in here, you have to know that you are on number seven. Otherwise, you have to sort this list and figure out what it is, or go through it and find out. For a small list, that is not a big deal if you just want to sort 10 of these. But if you have 10,000 customers and you want to put a custom sort order in for them, then you might want some help figuring out what that next number is. I will show you that as a bonus for members only. I will show you how to make a form, a continuous form with all of your items in it. When you go to add the next item, it will automatically put the next item in the sort list. How do you become a member? Just click on that join button right now. You will get a list of all the different types of memberships that are available. Silver members and up get access to all of the members only extended cut videos. But do not worry, I am still going to keep posting free TechHelp videos just like this one. Make sure you subscribe to my channel so you get notifications whenever I release a new video. Make sure to stop by my website and check out the Access forum. Of course, if you have not yet, check out my free 3-hour long full Access Level 1 tutorial. It is 3 hours of all the basics. If you like that, Level 2 is just $1. Thanks for watching. I hope you learned something, and we will see you next time. QuizQ1. What is the main technique shown in the video for creating a custom sort order in Access?A. Adding a sort order field to your table B. Sorting alphabetically by company name C. Using a macro to define custom sorting D. Creating a new database for sorting Q2. Why should you avoid naming a field "order" in Access? A. It is not a descriptive name B. "Order" is a reserved word in Access C. It is too short for a field name D. Access does not allow numeric fields Q3. What data type is recommended for the custom sort order field initially? A. Text B. Date/Time C. Number D. Currency Q4. If you want to prevent duplicate sort order numbers, what feature should you use? A. Set the field to Required B. Add a unique index to the sort order field C. Format the field as text D. Make the field a primary key Q5. If you want more flexibility in your sort order numbers (like inserting 5.1 between 5 and 6), what should you do? A. Change the field to text B. Change the field to a double or decimal data type C. Add more fields for sorting D. Use only whole numbers Q6. How do you use a query to sort your companies by the custom sort order? A. Group by the sort order field B. Add the sort order field and set the sort to Ascending or Descending C. Sort only by company name D. Do not use queries for custom sorting Q7. What is a potential difficulty when using a custom sort order with large lists? A. It takes too long to open the database B. Remembering or finding what the next sort order number should be C. The field cannot handle large numbers D. Access does not allow sorting on more than 100 items Q8. What bonus tip did Richard offer for members regarding assigning sort orders? A. How to write VBA code for sorting B. How to automatically assign the next sort order number using a form C. How to remove duplicates automatically D. How to alphabetize by default Q9. What types of reports and forms can you base on the sorted query? A. Only mailing labels B. Only reports C. Any forms, queries, and reports that use the data D. Only forms Q10. What is suggested if you have a small list for custom sorting? A. Manually enter the sort order numbers B. Skip adding a sort order C. Use alphabetical sorting D. Use an external application Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 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. SummaryToday's video from Access Learning Zone focuses on how to create a custom sort order in Microsoft Access. Often, people want to sort records in a way that doesn't follow the standard alphabetical or numerical order. For example, maybe you have a specific list of companies that you want to appear in a particular sequence on your reports or forms, different from how Access would naturally sort them.This question comes up frequently, and the solution is actually quite simple. All you have to do is add a new field to your table specifically for your custom sort order. For instance, imagine you have a list of customers, but you want them grouped in a particular way that does not depend on first or last names, or anything else already in the table. To accomplish this, just add an extra field to your table, such as 'SortOrder,' and then manually enter numbers that correspond to the sequence you want. Let me walk through how you might set this up with a fresh table. When designing your company table, include fields for the company ID (set to auto number), the company name, and then a field called 'SortOrder' or something similar. It's important not to use 'Order' by itself as a field name, since that's a reserved word in Access and could cause issues. Set the data type of your custom sort order field to 'Number.' You can leave the default value at zero if you'd like, but you'll need to enter the actual values yourself when you're deciding on the order. Once your table is ready, enter your list of companies. For example, you might add 'Microsoft,' 'IBM,' 'Apple,' 'Tesla,' 'Ford,' and 'Computer Learning.' Next, assign a unique sort number to each company, according to your desired order. For example, you could decide that 'Computer Learning Zone' comes first, followed by 'Tesla,' then 'Ford,' and so on. If you use the same number for more than one company, Access won't guarantee which one comes first unless you add a secondary sort, such as by company name. If you want to ensure that there are no duplicate sort order numbers, you can set an index on the sort order field with no duplicates allowed. However, for many small lists, that's not always necessary. For even more flexibility, you could change the field type to decimal or double, allowing you to insert values like 5.1 if you need more detail without renumbering everything. With a custom sort order in place, you can now sort your queries and reports based on the values in this field. When building a query, just add the company ID, company name, and sort order fields, and specify sorting by your custom sort field. This gives you the sequence exactly as you specified in your table. You could then base reports, mailing labels, or other objects on this query for your custom order. One thing to keep in mind is that if you add another company, you need to keep track of the next available sort order number. For smaller lists, this is straightforward, but for larger ones you might want a way to automate finding the next available slot. As a bonus for my members, I'll demonstrate how to build a form that makes assigning the next sort order number easier. With a continuous form, when you add a new entry, it will automatically suggest the next number. If you want access to more in-depth tips like this, consider becoming a member. There are different membership levels available, and Silver members and higher can view all the exclusive extended cut videos. For everyone else, I will continue publishing free TechHelp videos regularly, so make sure to subscribe to get notified of new content. Don't forget to visit my website where you can join the Access forum and connect with others working with Microsoft Access. If you're new to Access, be sure to check out my free Level 1 course, which covers three hours of introductory material. If you like what you see, the next level is just one dollar. 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 ListCreating a custom sort order field in a tableSetting up a new table with a sort order field Populating the table with sample company data Assigning custom sort order values Indexing the sort order field to prevent duplicates Changing the sort order field to allow decimals for granularity Sorting data in queries based on the custom order field Using the custom sort order in reports and forms |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access Custom Sort Order PermaLink Custom Sort Order in Microsoft Access |
