Learn Access Now!      Chapter 4      Next Section in Chapter 5      Chapter 6

Chapter 5: Sorting and Filtering Information

(This is section 1 of 3 in this chapter)

To let you quickly manipulate information in a table and display it in a more organized fashion, Access provides two special tools: sorting and filtering. Using these tools, you can work with your information in whatever way makes sense to you. This chapter will explain how sorting and filtering work, as well as how you use these tools to work with your data.

By the time you finish the chapter, you will understand the following key concepts:

Sorting Your Data

Sorting is the process of putting your data into a logical order. We each sort things every day. For example, you may arrange your personal bills by date so that you can handle the most urgent ones first. You may arrange your video tapes in alphabetical order or your music collection by artist name. In your kitchen, you may arrange your silverware into bins: forks in one bin, spoons in another, knives in a third. In each of these cases, you are sorting, although you may not think of it that way, at first.

With Access, you can perform simple and complex sorts The next two sections will address each of these sort operations in detail.

Simple Sorting

Within Access, you will sort (or order) records in a table based on one or more fields. For example, you might sort records by last and first names so that all your friends with the last name Smith are sorted by their first names.

A simple sort orders records based on the contents of one or more adjacent fields. When you perform a simple sort, you must specify the order in which you want Access to sort your records:

For example, assume you have a table of your favorite video tapes. If you want to order the records based on the titles of your video tapes, you ask Access to perform a simple sort. To perform a simple sort based on a single field, select a field (cell) in the column by which you want to sort. Next, use either of the following actions, depending on how you want to sort:

Figure 5-1 The sorting tools on the toolbar.

When you perform the sort, Access reorganizes your table so that the records are in the order you specified.

Note: Depending on the number of records in your table, it may take some time for Access to sort your data. The length of time necessary also depends on the speed of your computer and hard drive.

You can also use the simple sorting feature to sort by more than a single field. However, these columns must be adjacent to each other, and each field must be sorted in the same manner (ascending or descending). In other words, you can't sort one column in ascending order and the other in descending order.

If you want to sort one column in one order and another in a different order, you will need to use a complex sort, discussed later in this chapter. Also, if the columns are not adjacent to each other, you must use a complex sort.

As an example of using the simple sorting feature with adjacent columns, assume you have assigned numbers to every tape in your video collection. Also, assume each record represents a single show. (Keep in mind that Access may use the same tape number in more than one record. This would happen when a video tape contains more than one show.) What if you want to sort the table by tape number and then show name? If the columns that contain these two fields were adjacent to each other, you would follow these steps:

  • Move the mouse pointer so it is over the column name (at the top of the Datasheet) for one of the columns by which you want to sort. When you do this, the pointer changes to an arrow pointing down.
    1. Click your mouse on the left mouse button. This selects (highlights) the column.
    2. Hold down the SHIFT key and repeat steps 1 and 2 with the other column. Access will highlight all the columns between the first and the second one you selected.
    3. Use one of the sorting methods (ascending or descending) described in the previous example.

    When you perform a simple sort using multiple columns, Access works from left to right through your table. Thus, Access first sorts the leftmost column, moves to the right and sorts the next column, and so on.

    Moving Columns

    Before you can perform a simple sort on multiple fields (columns), you may need to move the columns so they are adjacent. When you are working in the Datasheet, you can use the mouse to reorganize (move) columns in your table. To move a column, follow these steps:

    1. Select the column you want to move and leave the mouse pointer on that column.

    2. Hold down the left mouse button. A small, shaded box will appear around the tail of the mouse pointer.

    3. Drag the column to a new position in the table. As you move the mouse left or right, Access highlights the lines between columns to indicate where the column will be moved.

    4. When the mouse pointer is over the position you desire, release the mouse button. Access, in turn, moves the column to the new position.

    Complex Sorting

    In the last section, you learned about simple sorting. Complex sorting goes a step further. You can use a complex sort to sort multiple, non-adjacent columns in different orders (ascending and descending). For example, if you have a table of names and addresses, you may want to order the records first by ZIP code and then by last name (within ZIP code). If the two columns are not adjacent (with the ZIP code column to the left of the last name column), you need to use a complex sort.

    To accomplish a complex sort, you use the Filter window. (This may sound like a strange place, but it is--nonetheless--the proper place. Filters are described in detail later in this chapter.) To display this window, select Filter from the Records menu, and then Advanced Filter/Sort from the resulting submenu. Access displays the Filter window, shown in Figure 5-2.

    Figure 5-2 The Filter window.

    To perform a complex sort, you need to choose which fields you want to sort and then specify how to sort them. For example, assume you are working with the Business Customers table (developed in Chapter 4, "Creating Another Database"), and you want to sort it first by ZIP Code and then by company name.

    To do this, you need to specify these fields and their sort order in the grid at the bottom of the Filter window. In the first column of the Filter window, click your mouse on the arrow at the right of the field box for that column and select the Zip Code field.

    Next, you need to tell Access you want the Zip Code field sorted in ascending order. To do this, click your mouse in the Sort row beneath where you placed the Zip Code field and then click your mouse on the arrow at the right of the cell. Access, in turn, provides a drop-down list from which you can pick Ascending as the sort order.

    After you select the order for this sort, the Filter window will appear as shown in Figure 5-3.

    Figure 5-3 The Filter window after specifying the first field to sort by.

    In the second column, you can specify the second field by which you want to sort (Company Name) and how you want to sort it. If you want to sort the table in other ways, you can continue to select other fields and specify how to sort them. When you are done, you need to apply the sort to the table. To apply the sort, select the Apply Filter/Sort option from the Filter menu, or click your mouse on the Apply Filter tool. Access then displays the records in your table in the specified order.

    Within the Filter window, there are other ways you can add fields to the sort, other than the method you just used (clicking your mouse on the pull-down arrow for the field list). For example, if you double-click your mouse on a field name in the list at the top of the Filter window, the field name will appear in the first empty column in the Field row. As shown in Figure 5-4, for example, if you double-click your mouse on the field name Main Phone, Access will place the field name into the first unused filter column.

    Figure 5-4 You can double-click your mouse to select fields.

    You can also use your mouse to drag field names from the field list to the grid area. All in all, Access provides three or four different ways to pick the fields you want to sort by. If you later want to remove a field from the sort, simply use your mouse to select the column containing the field (in the grid area), and then press the DEL key or choose Edit menu Delete Column option.

    Note: To undo the effects of a sort, choose the Remove Filter/Sort option from the Records menu.

    Learn Access Now!      Chapter 4      Next Section in Chapter 5      Chapter 6