In this chapter, you will learn to: | |
✓ | Sort and filter information in a table. |
✓ | Filter information by using a form. |
✓ | Locate information that matches multiple criteria. |
✓ | Create a query manually or by using a wizard. |
✓ | Perform calculations by using a query. |
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 Office Access 2007 provides a variety of tools you can use to organize the display of information stored in a database and to locate specific items of information. Using these tools, you can organize all the records in a table by quickly sorting it based on any field or combination of fields, or you can filter the table so that information containing some combination of characters is displayed or excluded from the display. With a little more effort, you can create queries to display specific fields from specific records from one or more tables. You can save queries and run the saved queries to generate updated results when data changes.
In this chapter, you will learn how to sort and filter information in a table, display selected information in a form; and locate information that matches multiple criteria. Then you will create queries to locate information and to perform calculations.
See Also
Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries at the beginning of this book.
Important
Before you can use the practice files in this chapter, you need to install them from the book’s companion CD to their default location. See "Using the Book’s CD" at the beginning of this book for more information.
Troubleshooting
Graphics and operating system–related instructions in this book reflect the Windows Vista user interface. If your computer is running Microsoft Windows XP and you experience trouble following the instructions as written, please refer to the "Information for Readers Running Windows XP" section 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 | First |
|---|---|
Smith | Denise |
Smith | James |
Smith | Jeff |
Thompson | Ann |
Thompson | Steve |
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.
In this exercise, you will sort records first by one field, and then by multiple fields.
Note
USE the 01_SortTable database. This practice file is located in the Chapter11 subfolder under SBS_Office2007.
OPEN the 01_SortTable database.
In the Navigation Pane, under Tables, double-click Customers.
The Customers table opens in Datasheet view.
Click the arrow at the right side of the Region column header, and then click Sort A to Z.
Access rearranges the records in alphabetical order by region, and displays a narrow upward-pointing arrow at the right side of the column header to indicate the sort order.
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 your list. In both sorts, the region was sorted alphabetically, but the City field was left in a seemingly random order.
Suppose that you want to see the records arranged by city within each region. You can do this by sorting the City column and then the Region column, or by moving the Region column to the left of the City column, selecting both, and then sorting them together.
To sort the cities in ascending order within the regions, first click the City sort order arrow, and then click Sort A to Z.
Access sorts the records alphabetically by city.
To finish the process, right-click anyplace in the Region column, and then click Sort A to Z.
The two columns are now sorted so the cities in each region are listed in ascending order.
To sort both columns at the same time in descending order, move the Region field to the left of the City field by clicking its header to select the column, and then dragging the column to the left until a dark line appears between Address and City. Release the mouse button to complete the move operation.
With the Region column selected, hold down the
key and click the City header to extend the selection so that both the Region and City columns are selected.In the Sort & Filter group, click the Descending button to arrange the records with the regions in descending order and the city names also in descending order within each region (or in this case, each state).
Experiment with various ways of sorting the records to display different results.
