The records in an Access data table can be sorted and arranged in any order. In this video, Adam Wilbert shows how to organize the table so the most important records appear at the top. Also, learn how to properly identify the position of a record in a database table, not by its sort order, but by its primary key value.
- [Instructor] A common task with any table of data is to sort contents to bring relevant records to the top. On the home tab of the ribbon, we have a group called sort and filter that includes the tools to do just that. Let's take a look at these options in the Guest table, I'll just go ahead and double click on it to open in up in data sheet view. Now right now, our records are in the order they were put into the database. With the first record, Guest ID number one that belongs to Katherine Reed here at the very top. And if I go down to the bottom of the table by pressing on the last record button, we'll see that record 201, or Guest ID number 201, Michael Adkins, is at the very bottom.
Let's go back up to the top. I can sort these records based on any field that I choose. To put them alphabetically by last name, you can click in any of the last name boxes or click on the header at the very top. Then, come up to the sort and filter group and press either the ascending or descending button. I'll press ascending to sort them A to Z. Now we can see that Roy Adams floats to the top of the table. To remove the sort just press the remove sort button and the table returns to its original state. These options are also available in the menu when you click on the drop down arrow here to the right of each of the field names.
Or if you right click on the field name you'll see the same sorting options there. Notice when you have a sort applied that you get an upward pointing arrow or a downward pointing arrow to the right of this arrow. This is your visual cue that the table is currently sorted based off of this field. Let's go ahead and remove that sort again. So sorting the data in your tables is a pretty straight forward task but it brings up a couple of important conceptional points that I'd like to mention about how records are organized in your tables. Remember that records, that's the horizontal rows in your data tables, represents a single entity, thing or event.
Each cell in a row goes with the others in that row. It wouldn't make any sense to mix them up, so when I sort these fields, wherever Katherine goes for example, the last name Reed and all of these other details goes right along with it. They move in the sorting order together, up and down, as an unbreakable unit. In Access, there's no way to mess that up and accidentally sort a single column one way and lose the relationship to its adjacent information in the other columns. The other important concept here is the idea of a record's position in the table. Right now, Katherine Reed is record number one out of 201 and I can see that again by taking a look at this box at the very bottom.
She's record number one because she's at the top of the list, not because she's GuestID number one. If I sort it again based off of the last name, ascending, there's Roy Adams again here at the top, he's Guest ID number 115. But I can now that he is in position number one of 201. A record's position in the table is a very transitive thing. Anytime we talk about a record's position, we really mean in this sorting order, this is record number one. That's one of the main reasons we use primary keys. When I say GuestID number one, I'll always be referring to Katherine Reed regardless of how the table is sorted.
When I say record number one, it can refer to any number of people depending on how they are currently sorted. It's important to understand that a specific record can appear in any order in the table and the way they are stored on disc has no relevance to how they are located in the database.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.