Join Adam Wilbert for an in-depth discussion in this video Filtering table data, part of Access 2016 Essential Training.
- The Filtering options on a table allow you to limit the number of records on screen to only those of interest. Just like with Sorting, this allows you to quickly get to the information that you need. Let's work with the Guests table again. I'll double-click on it here in the navigation pane, and, once again, we're going to return our attention to the Home tab and the Sort & Filter section right over here. Now let's say that I wanted to find all of the guests that live in the country of China. In order to do that, one of the easiest ways is to find one of these people. In this case, this person here, Anne Harvey. We'll go ahead and click on this field that says China, and we can come up here to the ribbon.
We have a Filter by Selection option here, it's a drop-down menu and we have the options of Equals "China", Does Not Equal "China", Contains "China", or Does Not Contain "China". Let's go ahead and say Equals "China", and that will Filter out my data table to just the people who live in the country of China. You can notice on the bottom that I get some information about this grouping. We have a total of 19 people that this applies to. And we also have a button here that says Filtered, so that's a good visual indicator that I'm not looking at all the records in this table; I'm just looking at a Filtered selection of records.
If I want to Toggle back and forth between the Filtered and Unfiltered state, I can just press this button right here, and that will return to the Unfiltered state where I see all 201 of my guests, and then I can also press this button again to return to the last Filter that I just applied. In this case, just on the country of China. Now if I come up here to the column header where it says Country, you'll notice I have a Filter icon here, a little funnel. If I click that, you'll notice we get some additional options. I can go to the text filters and I have the same options that we saw just a moment ago plus a couple more.
I can also go through and select individual countries so instead of just selecting China, maybe I'll scroll through and choose the United States as well. Scroll to the bottom of the list and place a check mark here and say OK. Now I can look on the bottom and I see I have a total of 122 guests that live either in China or the United States. And just like with this button down here on the bottom, I also have one on the ribbon, this one up here that says Toggle Filter, I can Toggle Filter on and off this way as well. We can also apply Filters on multiple columns at the same time, so if I wanted to find all the people who live in the United States or China that have the last name of Dixon, I can either find somebody who has that last name, for instance here's Patricia Dixon, and I can click there and say Selection Equals "Dixon".
The other option is to come over here to the LastName column here, click on the drop-down menu, or, incidentally, we can right-click, but I'll use the drop menu and we can go to turn off Select All, so it'll uncheck everybody, and then we'll just scroll down until we get to the Ds, and we'll put a check mark next to Dixon. We'll say OK, and we see we have a total of three people with the last name of Dixon that either live in China or the United States. And, once again, we come up here to this Toggle Filter button and return back to the Unfiltered state of our data, and press it again to return to the last current Filter that we have established right now.
For a quick exploration of your data, a Filter can bring out just what you need in a hurry. While you can Toggle between a Filtered and an Unfiltered state, there's no real way to save multiple Filters and move back and forth between those. Now at first, this might seem like it would be a really useful feature and an obvious oversight, but rest assured, you'll see why this is when we get into exploring Query objects. While you can Filter your data here inside of the data table directly, Queries really do take that functionality to the next level.
The course also shows you how to build queries and action queries, create and design forms, use macros, integrate Access with the rest of the Office 2016 suite, and maintain your databases over time.
- Creating a new database
- Creating tables and new data types
- Importing and entering data
- Setting up relationships and primary keys
- Adding validation rules
- Sorting and filtering table data
- Building queries
- Designing forms
- Creating reports
- Attaching macros to buttons and tables
- Working with Excel and Outlook data
- Maintaining an Access database
Skill Level Beginner
1. Getting Started
2. Creating Tables
3. Setting Field Properties
4. Organizing Records
5. Using Queries
6. Working with Specialty Queries
7. Creating Forms
8. Designing Forms
9. Creating Reports
10. Working with Macros
11. Integrating Access with Office Suite
12. Maintaining the Database
Next steps1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.