MS Access 2003 - Lesson 18: Chapter 5: Sorting and filtering information

In order for you to quickly apply information in the table and display it in a more organized way, Access provides two special tools: sorting and filtering. Using these tools, you can work with information in every way. This chapter explains how to perform sorting and filtering, as well as how to use these tools to work with data.

At the end of this chapter, you will understand the following:

1. What is arrangement?
2. How to make a simple arrangement
3. What is a simple arrangement?
4. Ways to build a complex arrangement.
5. How to remove the effects of an arrangement.
6. What is a filter?
7. How to filter data.
8. How to save a filter.

Lesson 18: Arrange data

Arrangement is the process of placing data in the correct logical order. We used to organize things every day. For example, you arrange individual invoices by date so you can manage important things first. You arrange the video sequences in alphabetical order or gather songs by artist name.

With Access, you can perform sorting in a simple and complex way that the next two sections will go into detail about those arrangements.

Simple arrangement

In Access, you will arrange records in a table based on one or more fields. For example, you must sort records by last name or first name so that all your friends whose last name is Smith have been sorted by their own name.

Arrange the records in order based on the content of one or more adjacent fields. When doing a simple sort, you have to specify the order in which you want Access to arrange for the record:

• Sort in ascending order: This means that Access will arrange the records in order from low to high. In that way, Access is about to place letters from A to Z and arrange the information in ascending order of numbers.

• Sort in descending order: This is the opposite of sorting in ascending order, and it is also called reverse order. Access arranges the order from high to low: alphabetic information from Z to A, by number from highest to lowest.

For example, suppose you have a table of favorite tapes. If you want to order records based on tape titles, you ask Access to make a simple sort. Make a simple sort based on a single field, select a field (cell) in the column you want to sort. Next, use the following actions, based on how you want to sort:

• To sort in ascending order, click your mouse on the Sort Ascending (AZ) tool as shown in Figure 1. You can select the Sort option from the Records menu and select Sort Ascending.

• To sort in descending order, click your mouse on the Sort Descending (ZA) tool as shown in Figure 1. You can select the Sort option from the Records menu and select Descending Sort.

MS Access 2003 - Lesson 18: Chapter 5: Sorting and filtering information Picture 1
Figure 1: Sort tool

When performing a sort, Access reorganizes the table so that the records are sorted in the order you want.

You can use simple sorting to sort more than a single field. However, those columns must be adjacent to each other, and each field must be arranged in the same level (ascending or descending). In other cases, you cannot arrange a column in ascending order and other columns in descending order.

If you want to arrange a column in a different order and column in a different order, you need to use complex sorting, mentioned later in this chapter. Just as if the column is not close together, you must use complex arrangements.

As an example using a simple sorting feature with adjacent columns, suppose you have assigned numbers to every tape in the set of videos and each record corresponds to a single display. (Remember, Access can use the same number of records more than one record. This happens when a video tape contains more than one display record). You want to sort the table by the number of tapes and then show the name like? If the columns contain two adjacent fields, you should do the following:

Move the mouse pointer so that it is above the name column (above the Datasheet mode) of one of the columns you want to sort. When doing this, the cursor changes to a downward pointing arrow.

1. Click the left mouse button to highlight the column.

2. Hold down SHIFT and repeat steps 1 and 2 with the other columns. Access displays all the columns between the first column and the second column you selected.

3. Use one of the arrangements (ascending or descending) described in the previous example.

When you make a simple sort using multiple columns, Access works from left to right through the table. Therefore, Access arranges the first leftmost column, moves to the right and arranges the next column, .

Move columns

Before making a simple sort on multiple fields (columns), you need to move the columns so that they are adjacent. When working in Datasheet mode, you can use the mouse to move the columns in the table. To do so, perform the following steps:

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

2. Hold the left mouse button. A small box will appear around the end of the mouse pointer.

3. Drag the column to the new position in the table. When moving the mouse left or right, Access displays the line between the columns to indicate where the column was moved.

4. When the mouse pointer is over the position you want, release the mouse button. In turn, Access moves the column to a new location.

Complex arrangement

In the previous section, you learned about simple arrangement. Complex arrangements are a step further. You can use a complex arrangement to arrange multiple columns, non-contiguous columns in different order (ascending and descending). For example, if you have a table of names and addresses, you can sort the first record by ZIP code (postal code) and by last name (by ZIP code). If two columns are not adjacent (Zip code column to the left of the last column name), you need to use complex sorting.

To complete the complex arrangement, use the Filter window. (Filter is described in detail after this chapter). To display the window, select Filter from the Records menu, then select Advanced Filter / Sort from the results of the submenu. Access displays the Filter window as shown in Figure 2.

MS Access 2003 - Lesson 18: Chapter 5: Sorting and filtering information Picture 2
Figure 2: Filter window

To make a complex arrangement, select the fields you want to sort, then specify how to sort them. For example, suppose you are working with the Customers' table (developed in Chapter 4, 'Creating another database'), and you want to sort it first by the Zip Code and then the company name.

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

Next, you need to indicate the Zip Code field is sorted in ascending order. To do so, click your mouse on the Sort line below where you placed the ZipCode field, then click the arrow to the right of the cell. In turn, Access provides a drop-down list and you can choose Ascending for the sort order.

After selecting the sort order, the Filter window will display as shown in Figure 3.

MS Access 2003 - Lesson 18: Chapter 5: Sorting and filtering information Picture 3
Figure 3: Filter window after specifying the first field to sort

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

In the Filter window, there are many different ways you can add fields to sort, different from the methods you used (click the scroll bar of the field list). For example, if you double-click a field name in the list above the Filter window, the field name will appear in the first blank column in the Field row. Figure 4, if you double-click the Main Phone field name, Access places the field name in the unused column.

MS Access 2003 - Lesson 18: Chapter 5: Sorting and filtering information Picture 4
Figure 4: You can double click the mouse to select the field

You can also use the mouse to drag the field name from the list to the area under the frame. Access provides three or four other ways to get the school you want to sort. If you later want to delete a field, simply use the mouse to select the column containing the field (in the frame area) and press the DEL key or select the Culumn Delete from the Edit menu.