Chapter at a Glance
A database is a repository for information. It might contain only a few records or thousands of records, stored in one table or multiple tables. No matter how much information a database contains, it is useful only if you can locate the information you need when you need it. In a small database, you can find information simply by scrolling through a table until you find what you are looking for. But as a database grows in size and complexity, locating and analyzing information becomes more difficult.
Microsoft Access 2010 provides a variety of tools you can use to organize the display of information stored in a database. For example, you can organize all the records in a table by quickly sorting it based on any field or combination of fields. You can also filter the table so that information containing a combination of characters is displayed or excluded from the display.
In this chapter, you’ll first sort information in a table based on one and two columns. Then you’ll explore three ways to filter tables and forms to display only the records that meet specific criteria.
Note
Practice Files Before you can complete the exercises in this chapter, you need to copy the book’s practice files to your computer. The practice file you’ll use to complete the exercises in this chapter is in the Chapter28 practice file folder. A complete list of practice files is provided in Using the Practice Files at the beginning of this book.
You can sort the information stored in a table based on the values in one or more fields, in either ascending or descending order. For example, you could sort customer information alphabetically by last name and then by first name. This would result in the order found in telephone books.
|
Last Name |
First Name |
|---|---|
|
Smith |
Brian |
|
Smith |
Denise |
|
Smith |
Jeff |
|
Taylor |
Daniel |
|
Taylor |
Maurice |
Sorting a table groups all entries of one type together, which can be useful. For example, to qualify for a discount on postage, you might want to group customer records by postal code before printing mailing labels.
Access can sort by more than one field, but it always sorts sequentially from left to right. You can sort by the first field, and if the second field you want to sort by is to the right of the first, you can then add the next field to the sort. If you want to sort by more than one field in one operation, the fields must be adjacent, and they must be arranged in the order in which you want to sort them.
Note
See Also For information about moving fields, see Manipulating Table Columns and Rows in Chapter 26.
Tip
You can sort records while viewing them in a form. Click the field on which you want to base the sort, and then click the Sort command you want. You can’t sort by multiple fields at the same time in Form view, but you can sort by one field and then the next to achieve the same results.
In this exercise, you’ll sort records first by one field, and then by multiple fields.
Note
SET UP You need the GardenCompany04_start database located in your Chapter28 practice file folder to complete this exercise. Open the GardenCompany04_start database, and save it as GardenCompany04. Then follow the steps.
With All Access Objects displayed in the Navigation pane, under Tables, double-click Customers.
The Customers table opens in Datasheet view.
Click the arrow to the right of the Region field name.
A list of sorting and filtering options appears.
Click Sort A to Z.
Access rearranges the records in alphabetical order by region.
To reverse the sort order by using a different method,
on the Home tab, in the Sort & Filter group, click the Descending button.The sort order reverses. The records for customers living in Washington (WA) are now at the top of the list, and the arrow at the right end of the field name is pointing downward.
In both sorts, the region was sorted alphabetically, but the City field was left in a seemingly random order. Suppose you want to see the records arranged by city within each region. You can do this by sorting the City field and then sorting the Region field.
Click the arrow to the right of the City field name, and then click Sort A to Z.
Access sorts the records alphabetically by city.
To finish the process, right-click anywhere in the Region column, and then click Sort A to Z.
The two fields are now sorted so that the cities are listed in ascending order within each region.
On the Home tab, in
the Sort & Filter group, click the
Remove Sort button to clear the sort from both
fields.The table reverts to the previously saved sort order. Now let’s sort both columns at the same time.
Click the City field
name, hold down the Shift key, and click the Region field name. Then in the Sort
& Filter group, click the Ascending button.Because the City field is to the left of the Region field, Access cannot achieve the result you want.
Clear the sort, and then click away from the City and Region fields to clear the selection.
Click the Region field name, and drag the field name to the left of the City field name, releasing the mouse button when a heavy black line appears between the Address and City field names.
With the Region field selected, hold down the Shift key, and click the City field name to include that field in the selection.
In the Sort & Filter group, click the Ascending button.
Access arranges the records with the regions in ascending order and the cities in ascending order within each region.
Experiment with various ways of sorting the records to display different results. Then close the Customers table, clicking No when prompted to save the table layout.
Sorting the information in a table organizes it in a logical manner, but you still have the entire table to deal with. For locating only the records containing (or not containing) specific information, filtering is more effective than sorting. For example, you could quickly create a filter to locate only customers who live in Seattle, only items that were purchased on January 13, or only orders that were not shipped by standard mail. When you filter a table, Access doesn’t remove the records that don’t match the filter; it simply hides them.
The Filter commands are available in the Sort & Filter group on the Home tab, on the menu displayed when you click the arrow at the right end of a field name, and on the shortcut menu displayed when you right-click anywhere in a field’s column. However, not all Filter commands are available in all of these places.
To filter information by multiple criteria, you can apply additional filters to the results of the first one.
Tip
You can filter records while displaying them in a form by using the same commands as you do to filter records in a table.
In this exercise, you’ll filter records by using a single criterion and then by using multiple criteria.
Note
SET UP You need the GardenCompany04 database you worked with in the preceding exercise to complete this exercise. Open the GardenCompany04 database, and then follow the steps.
In the Navigation pane, under Tables, double-click Customers to open the Customers table in Datasheet view.
On the Home tab, in
the Sort & Filter group, click the
Selection button, and then in the
list, click Equals
“Vancouver”.Access displays a small filter icon shaped like a funnel at the right end of the City field name to indicate that the table is filtered by that field. The status bar at the bottom of the table has changed from 1 of 110 to 1 of 6 because only six records have the value Vancouver in the City field. Also on the status bar, the Filter status has changed to Filtered.
Tip
In the list displayed when you click the arrow
to the right of a field name (or the Filter button
in the Sort & Filter group) are check boxes for all the unique
entries in the active field. Clearing the Select All check box
clears all the boxes, and you can then select the check boxes of any
values you want to be displayed in the filtered table.In the Sort & Filter group on the Home tab, the Toggle Filter button is now active. You can use this button to quickly turn the applied filter on and off.
In the Sort &
Filter group, click the Toggle
Filter button.Access displays all the records. If you click the Toggle Filter button again, the filter will be reapplied.
Now let’s display a list of all customers with postal codes starting with 880.
Click the arrow to the right of the PostalCode field name, and point to Text Filters in the list.
Tip
The sort and filter options displayed when you click the arrow to the right of a field name (or when you click the Filter button in the Sort & Filter group) are determined by the data type of the field. The PostalCode field is a Text field to allow for ZIP+4 codes. If you display the sort and filter list for a field that is assigned the Number data type, the sort and filter list includes Number Filters instead of Text Filters, and different options are available.
In the list, click Begins With.
In the PostalCode begins with box, type 880. Then click OK.
Access filters the table and displays only the records that match your criteria.
In the Sort & Filter group, click the Toggle Filter button to remove the filter and display all the records.
Now let’s display only the records of the customers who live outside of the United States.
In the Country field, right-click any instance of USA, and then click Does Not Equal “USA”.
Tip
In this case, it is easy to right-click the
text you want to base this filter on. If the text is buried in a
large table, you can quickly locate it by clicking the Find button in the Find group on
the Home tab, entering the term you want in the Find What box in the
Find And Replace dialog box, and
then clicking Find Next.Access displays the records of all the customers from countries other than the United States (in this case, only Canada).
Remove the filter, and close the Customers table, clicking No when prompted to save your changes.
Open the Orders table in Datasheet view.
In the EmployeeID field, right-click 7, and then click Equals 7.
Twenty records are displayed in the filtered table.
In the OrderDate field, right-click 2/1/2010, and then click On or After 2/1/2010.
Tip
To see a list of the available options for date filters, right-click any cell in the OrderDate field, and then point to Date Filters.
You now have a list of the orders customers placed with the selected employee on or after the specified date. You could continue to refine the list by filtering on another field, or you could sort the results by a field.
Close the Orders table, clicking No when prompted to save the table layout.
When you want to filter a table based on the information in several fields, the quickest method is to use the Filter By Form command, which is available from the Advanced Filter Options list in the Sort & Filter group on the Home tab. When you choose this command with a table displayed, Access displays a filtering form that resembles a datasheet. Each of the cells in the form has an associated list of all the unique values in that field in the underlying table.
For each field, you can select a value from the list or type a value. When you have finished defining the values you want to see, you click the Toggle Filter button to display only the records that match your selected criteria.
Using Filter By Form on a table that has only a few fields, such as the one shown above, is easy. But using it on a table that has a few dozen fields can be cumbersome, and it is often simpler to find information in the form version of the table. When you choose the Filter By Form command with a form displayed, Access filters the form the same way it filters a table.
After you have applied the filter, you move between the matched records by clicking the buttons on the record navigation bar at the bottom of the form page.
In this exercise, you’ll filter a form by using the Filter By Form command.
Note
SET UP You need the GardenCompany04 database you worked with in the preceding exercise to complete this exercise. Open the GardenCompany04 database, and then follow the steps.
In the Navigation pane, under Forms, double-click Customers.
On the Home tab, in
the Sort & Filter group, click the
Advanced Filter Options button, and then in the list, click
Filter By Form.The Customers form is replaced by its Filter By Form version, which has two pages: Look For and Or. Instead of displaying the information for one record from the table, the form now has a blank box for each field.
Click the second text box to the right of the Name label (the box that normally displays the customer’s last name), type s*, and then press Enter.
The asterisk is a wildcard that stands for any character or string of characters. Access converts your entry to Like “s*”, which is the proper format, called the syntax, for this type of criterion.
Note
See Also For information about wildcards, see the sidebar Wildcards following this topic.
- In the Sort &
Filter group, click the Toggle
Filter button.
Access displays the first record that has a LastName value starting with S.
Click the Advanced Filter Options button and then click Filter By Form to redisplay the filter form.
Click the second box to the right of the City/State/ZIP label (the box that normally displays the state or region), click the arrow that appears, and then in the list, click CA.
You are instructing Access to find and display records that have both a Region value of CA and LastName values starting with S.
Click the Toggle Filter button.
Access displays the first of three records that meet the filtering criteria.
Switch back to the filter form, and at the bottom of the form page, click the Or tab.
The criteria you entered on the Look For page are still there, but on this page, all the fields are blank so that you can enter alternatives for the same fields.
Type s* in the second Name box, and click WA in the list for the second City/State/ZIP box.
You are instructing Access to find and display records that have either a Region value of CA and LastName values starting with S, or a Region value of WA and LastName values starting with S.
Use the record navigation bar to view the six records in the filtered Customers form.
Click the Toggle Filter button to remove the filter. Then close the form.
As long as your filter criteria are fairly simple, filtering is a quick and easy way to narrow down the amount of information displayed in a table or to locate information that matches what you are looking for. But suppose you need to locate something more complex, such as all the orders shipped to Midwestern states between specific dates by either of two shippers. When you need to search a single table for records that meet multiple criteria, or when the criteria involve complex expressions, you can use the Advanced Filter/Sort command, available from the Advanced Filter Options list.
Choosing the Advanced Filter/Sort command displays a design grid where you enter filtering criteria. As you’ll see, filters with multiple criteria are actually simple queries.
Note
See Also For information about Access 2010 features not covered in this book, refer to Microsoft Access 2010 Step by Step by Joyce Cox and Joan Lambert (Microsoft Press, 2010).
In this exercise, you’ll filter a table to display the data for customers located in two states. Then you’ll experiment with the design grid to better understand its filtering capabilities.
Note
SET UP You need the GardenCompany04 database you worked with in the preceding exercise to complete this exercise. Open the GardenCompany04 database, and then follow the steps.
In the Navigation pane, under Tables, double-click Customers to open the Customers table in Datasheet view.
- On the Home tab, in
the Sort & Filter group, click the
Advanced Filter Options button, and then in the list, click
Advanced Filter/Sort.
The CustomersFilter1 page opens, displaying the Query Designer with the Customers field list in the top pane and the design grid in the bottom pane.
In the Customers field list, double-click LastName to copy it to the Field row of the first column of the grid.
In the Criteria row of the LastName field, type s*, and then press Enter.
Because you have used the * wildcard, Access changes the criterion to Like “s*”.
In the Customers field list, double-click Region to copy it to the Field row of the next available column of the grid.
In the Criteria row of the Region field, type ca or wa, and then press Enter.
Tip
If you want to find the records for customers who live in California or Oregon, you cannot type ca or or, because Access treats or as a reserved word. You must type ca or “or” in the Criteria row. Anytime you want to enter a criterion that will be interpreted as an instruction rather than a string of characters, enclose the characters in quotation marks to achieve the desired results.
Your entry changes to “ca” Or “wa”. The query will now filter the table to display the records for only those customers with last names beginning with the letter S who live in California or Washington.
- In the Sort &
Filter group, click the Toggle
Filter button to display only
records that match the criteria.
Access switches to the Customers table page and displays the filter results.
Click the CustomersFilter1 tab to switch to the filter page.
In the or row of the LastName field, type b*, and then press Enter.
We want to filter the table to display only the records for customers with last names beginning with the letter S or B who live in California or Washington.
In the Sort & Filter group, click the Toggle Filter button to apply the filter.
On the Customers table page, the result includes records for all customers with last names that begin with S or B, but some of the B names live in Montana and Oregon.
Click the CustomersFilter1 tab to switch to the filter page, and look carefully at the design grid.
The filter first works with the two criteria in the Criteria row and searches for customers with names beginning with S who live in California or Washington. Then it works with the criteria in the Or row and searches for customers with names beginning with B, regardless of where they live. To get the results we want, we need to repeat the criterion from the Region field in the Or row.
In the or row of the Region field, type ca or wa, and then press Enter.
Apply the filter.
Access switches to the Customers table page and displays only the records for customers with last names beginning with S or B who are located in California or Washington.
Close the Customers table, clicking Yes when prompted to save changes to the design of the table.
Tip
If you are likely to want to use a filter again, you can save it as a query. On the Home tab, in the Sort & Filter group, click the Advanced Filter Options button, click Save As Query, assign the query an appropriate name, and click OK. Then you can run the query to display the filtered results at any time.
You can sort a table in either ascending or descending order, based on the values in any field (or combination of fields).
You can filter a table so that information containing a combination of characters is displayed (or excluded from the display).
You can apply another filter to the results of the previous one to further refine your search.
The Filter By Form command filters a table or form based on the information in several fields.
You can use the Advanced Filter/Sort command to search a single table for records that meet multiple criteria.