Join Alicia Katz Pollock for an in-depth discussion in this video Four ways to filter data, part of Access 2010 Power Shortcuts.
- View Offline
Forms are designed to be your most frequently used tool while working with your data. For that reason, there are several ways of filtering your records so that you are only viewing the ones you need. Go down to the top Form in your navigation pane and double-click on Customers Order Form. This form is used to see all of the information about a customer and all of their orders all from one window. When I look in the bottom left hand corner, I can see that there's 31 records. So, let's say that I only want to look at my customers in Nevada. Click in the State field and then go up to the ribbon and click on Selection.
I have four options, Equals "NV", Does Not Equal "NV", Contains "NV" and Does Not Contain "NV". I will click on Equals "NV" and now I can see that I have two records, All Kinds of Taste and Niche Cuisine. When I want to see all the records again, I will click on the Toggle Filter button. Now notice when you look at it, you can see that its all lit up in orange, which helps me remember that my records are filtered if I can't find what I am looking for. I will click on Toggle Filter to turn it off. Now you can get those exact same options by right-clicking on the State field.
On this shortcut menu, I have here Equals, Does Not Equal, Contains and Does Not Contain. Now one of my favorite methods is called Filter By Form. It's buried under this Advanced button. Advanced, Filter By form. This command blanks out the form and I can type my criteria right in it. Nevada (NV) came up automatically but I am going to go ahead hit Backspace and delete that out. Let's go up and find all of the customers of my SalesRep Hinton. I can either pick him off of the list or I can start typing.
Notice that it auto fills. All I have to type in is Hi before it finds the one that I am looking for. Notice that Access puts quotes around it because I am doing a text query. I will go up to the ribbon and choose Toggle Filter and now I have the 6 customers from my SalesRep Hinton. Now, if I go back to Advanced and Filter By Form again, I can even filter on multiple criteria. If I go to State and put in Alaska, AK and then Toggle my Filter, now I find all of Hinton's customers in Alaska. There are two of them, Blue Vine and Ibila.
I will click on the Toggle Filter button again and I am back to all 31 records. Now, click in the SalesRep field. One last way to filter is to click on this big Filter button on the left hand side. This button gives you a pop up with check boxes for every value in that field. The top check box turns on or off all of the options. I like to turn off (Select All) and then turn on my desired options. I click OK and now I have 8 records for those 3 SalesReps, Davenport, Douglas and Collins.
Once again, my Toggle Filter button will bring me back to all 31 records. One of these methods will feel right to you and become your most utilized filtering technique.
- Creating new field with field templates
- Understanding errors
- Filtering data
- Aligning and distributing controls
- Customizing the interface
- Navigating quickly between records
- Speeding up data entry
- Analyzing a table for redundant data
- Creating a multi-field primary key
- Moving records with append and delete queries
- Transforming a query into a PivotTable or PivotChart
- Working with images, charts, and Smart Tags in forms
- Hiding duplicates in reports
- Formatting shortcuts
- Using macros