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.
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 Documents\Microsoft Press\Office 2003 SBS\Queries\Sort folder and can also be accessedby clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Objects bar, click Tables .
Double-click Customers to open the table in Datasheet view.
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.
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. |
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 .
Because Region is already selected, hold down the [SHIFT] key and click the City header to extend the selection so that both the Region and City columns are selected.
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. |
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.