Sorting Records

3 4

The qryPhoneNumbers query created in the section "The Simple Query Wizard," appears to sort the results by customer, but you haven't directly sorted the query. The Customers table's primary key is the CustomerID field, so Access sorts the records by that field. You can quickly change that order by simply applying a sort order to any field. In this section, we'll look at a few examples.

First open the qryPhoneNumbers query in Design view, click the arrow on the right side of CompanyName's Sort field to display its drop-down list, and then select Descending. On the Database toolbar, click the Run button to view the results shown in Figure 9-27. The list of records is the same as that shown in Figure 9-5 except that the records are reversed. Most sorts can be performed by a simple click, but Access is capable of more.

figure 9-27. sort the phone list in descending order by the companyname field.

Figure 9-27. Sort the phone list in descending order by the CompanyName field.

When you sort by more than one field, Access gives precedence to the leftmost field. As a result, a sort can have unexpected effects, or even no effect. For example, suppose you want the qryPhoneNumbers query to be sorted by the ContactName field in ascending order, and then you'd like the CompanyName field sorted in ascending order. To configure this sorting, you could set the ContactName field's Sort to Ascending, and then set the CompanyName field's Sort to Ascending, as shown in Figure 9-28.

figure 9-28. apply an ascending sort to the contactname and companyname fields.

Figure 9-28. Apply an ascending sort to the ContactName and CompanyName fields.

Unfortunately, the results, shown in Figure 9-29, aren't what you expected.

figure 9-29. the previous sort doesn't have the expected result.

Figure 9-29. The previous sort doesn't have the expected result.

Specifically, this query sorts the CompanyName field alphabetically, but the ContactName sort seems to have no effect. Access applied both sorts, just not the way you expected. First Access sorted by the CompanyName field because it's the leftmost field. Then Access sorted by ContactName, but because each CompanyName entry is unique, the sort had no effect.

To solve this sort problem, move the CompanyName field to the right of the ContactName field, as shown in Figure 9-30.

figure 9-30. move the companyname field to the right of the contactname field.

Figure 9-30. Move the CompanyName field to the right of the ContactName field.

tip


To move a column in the design grid, first click the column selector (the small horizontal bar at the top of the design grid column) to select the entire column. Next drag the column bar to a new position.

This time Access sorts the records by the ContactName field first and then sorts by the CompanyName, as shown in Figure 9-31.

figure 9-31. access sorted these records first by the contactname field.

Figure 9-31. Access sorted these records first by the ContactName field.

In this configuration, the CompanyName sort has no effect because the ContactName entries are unique. You could achieve the same results by leaving the structure alone and applying a single ascending sort to the ContactName field.

Troubleshooting - I'm having problems sorting Null values

Records often contain blank fields. Access will list Null values at the beginning of an ascending sort and at the ending of a descending sort. If you don't know whether the data contains Null values, the results might not be what you expect. Plan for Null values by including an Is Null or Is Not Null expression in the field's Criteria cell for a query. The Is Null expression will return only those records that are Null and therefore probably won't be useful in this context. The Is Not Null expression will exclude Null fields from the results. Keep in mind that a blank field isn't always the only field that can be considered Null. A field that contains the result of an expression that returns Null is also considered Null. An expression generally returns Null when some component in that expression refers to a Null field (or variables).

See Chapter 4, "Creating a Database," for more details on working with Nulls.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net