Chapter 5 at a Glance

A database is a repository for information. It might hold a few records in one table or thousands of records in many related tables. No matter how much information is stored in a database, 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 spot what you are looking for. But as a database grows in size and complexity, locating specific information becomes more difficult.

Microsoft Office Access 2003 provides a variety of tools you can use to organize the display of information in a database and to locate specific items of information. Using these tools, you can focus on just part of the information by quickly sorting a table 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 even save these queries so that you can use them over and over again, as the information in the database changes.

A query can do more than simply return a list of records from a table. You can use functions in a query that perform calculations on the information in the table to produce the sum, average, count, and other mathematical values.

Working with the GardenCo database, in this chapter you will learn how to pinpoint precisely the information you need in a database using sorting and filtering tools, and queries. Note that you cannot continue with the database from the last chapter; you must use the practice files on the companion CD-ROM.

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-ROM" for more information.

Sorting Information

Information stored in a table can be sorted in either ascending or descending order, based on the values in one or more fields in the table. You could, for example, sort a customer table alphabetically based first on the last name of each customer and then on the first name. Such a sort would result in this type of list, which resembles those found in telephone books:

Last	First
Smith	Denise
Smith	James
Smith	Jeff
Thompson	Ann
Thompson	Steve

Occasionally you might need to sort a table to group all entries of one type together. For example, to qualify for a discount on postage, The Garden Company might want to sort customer records on the postal code field to group the codes before printing mailing labels.

If a field with the Text data type contains numbers, you can sort the field numerically by padding the numbers with leading zeros so that all entries are the same length. For example, 001, 011, and 101 are sorted correctly even if the numbers are defined as text.

How Access Sorts

The concept of sorting seems pretty intuitive, but sometimes your computer’s approach to such a concept is not so intuitive. Sorting numbers is a case in point. In Access, numbers can be treated as text or as numerals. Because of the spaces, hyphens, and punctuation typically used in street addresses, postal codes, and telephone numbers, the numbers in these fields are usually treated as text, and sorting them follows the logic applied to sorting all text. Numbers in a price or quantity field, on the other hand, are typically treated as numerals.

When Access sorts text, it sorts first on the first character in the selected field in every record, then on the next character, then on the next, and so on—until it runs out of characters. When Access sorts numbers, it treats the contents of each field as a single value, and sorts the records based on that value. This tactic can result in seemingly strange sort orders. For example, sorting the list in the first column of the following table as text produces the list in the second column. Sorting the same list as numerals produces the list in the third column:

Original	Sort as text	Sort as number
1	1	1
1234	11	2
23	12	3
3	1234	4
11	2	5
22	22	11
12	23	12
4	3	22
2	4	23
5	5	1234

In this exercise, you will learn several ways to sort the information in a datasheet or a form.

BE SURE TO start Access before beginning this exercise.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSQueriesSort folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

1. On the Objects bar, click Tables.

2. Double-click Customers to open the table in Datasheet view.

3. To sort by Region, click anywhere in the Region column, and then click the Sort Ascending button.

   

   Tip

   You can also use the Sort Ascending or Sort Descending commands by pointing to Sort on the Records menu; or you can right-click the column in the datasheet and click either command on the shortcut menu.

   The records are rearranged in order of region.

4. To reverse the sort order, while still in the Region column, click the Sort Descending button.

   

   The records for the state of 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. What you really want to see is the records arranged by city within each region.

   Tip

   Access can sort on more than one field, but it sorts consecutively from left to right. So the fields you want to sort must be adjacent, and they must be arranged in the order in which you want to sort them.

5. To move the Region field to the left of the City field, click its header to select the column, and then click the header again and drag the column to the left until a dark line appears between Address and City.

6. Because Region is already selected, hold down the key and click the City header to extend the selection so that both the Region and City columns are selected.

7. Click the Sort Ascending button to arrange the records with the regions in ascending order and the city names also in ascending order within each region (or in this case, each state).

   Tip

   You can sort records while viewing them in a form. Click the box of the field on which you want to base the sort, and then click one of the Sort buttons. However, you can’t sort on multiple fields in Form view.

8. The order of the columns in the Customers table doesn’t really matter, so close the Customers table without saving changes.

CLOSE the GardenCo database.