Sorting Information in a Table

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.

Open table as spreadsheet

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.

How Access Sorts

The concept of sorting seems quite 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.

Open table as spreadsheet

Original	Sort as text	Sort as numerals
1	1	1
1234	11	3
23	12	4
3	1234	11
11	22	12
22	23	22
12	3	23
4	4	1234

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.

In this exercise, you will sort records first by one field, and then by multiple fields.

Use the 01_SortTable database. This practice file is located in the Chapter11 subfolder under SBS_Office2007.

Open the 01_SortTable database.

1. In the Navigation Pane, under Tables, double-click Customers.

The Customers table opens in Datasheet view.

2. 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.

3. 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.

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.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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).

9. Experiment with various ways of sorting the records to display different results.

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 on one field then the next to achieve the same results.

Close the Customers table without saving your changes, and then close the 01_SortTable database.