MS-Access / Getting Started

---

Sorting a query

You can sort or alphabetize the results of a query in several ways. The first way is to use the Sort row in the design grid. Use the Sort row to tell Access which field to use to sort the datasheet. The second way is to use the Sort Ascending and Sort Descending buttons on the datasheet toolbar.

If you sort a query by date, Access alphabetizes the months - which is usually not what you want. Reports, on the other hand, know how to put months in chronological order. If you have monthly data that you want to sort, a report is a better object to use than a query.

To sort by a field, display your query in Design view and follow these steps:

1. Move the cursor to the Sort row in the column that contains the field by which you want to sort the records that the query selects.
2. Display the drop-down list for the Sort row. Access displays the options for sorting: Ascending, Descending, and (not sorted).
3. Choose to sort in ascending order or descending order.

You can use the Sort row in the design grid to sort by more than one field. You may want to sort the records in the datasheet by last name, for example, but more than one person may have the same last name. You can specify another field (perhaps First Name) as the second sort key.

When you sort using more than one field, Access always works from left to right, first sorting the records by the first field (the primary sort key) that has Ascending order or Descending order in the Sort row, and then using the second sort key to sort any records that have the same primary sort-key value.

You cannot sort by the following field types: Memo, OLE, Attachments, or multivalue Data Type.

Viewing top values

If all you care about are the top values produced by a query, you can tell Access to find and display only those records. Use the Top Values box in the Design View toolbar to see the top records produced by the query. A value in the Top Values box specifies exactly how many records in the datasheet you want shown; a percentage shows you that percentage of the records that the query finds.

Note that using a percentage does not show values that fall in the top xpercent; it shows you the top xpercent of the values. Say you are looking at test scores of twenty students. The test scores fall between 0 and 100, but are mostly in the 80s and 90s. If you ask to see the top 20 percent, Access shows you the top 4 scores (20 percent of 20 records), not the scores that are 80 or above. To see the scores that are 80 or above, type the criterion >=80 in the Test Score column in the design grid.

To display the top values found by a query, follow these steps:

1. Create your query with all the fields and criteria that you need.
2. Choose the field you want to sort by, and then set the Sort row to either Ascending order or Descending order.
   Access uses this to figure out which top values you're looking for. For instance, if we sort products using the Selling Price field, and sort in Ascending order, the cheapest products are at the top of the datasheet. When we ask for the top five prices, we get the five cheapest products. To get the most expensive products, we sort in Descending order so the most expensive products appear at the top of the datasheet.
3. Change the Top Values option by typing in a value or a value followed by a percent sign.
   You can also choose a value from the drop-down list. To see the top three values, type in 10. To see the top 3 percent of the values, type in 3%.
4. Click the View button to see only the top values in the datasheet.

Hiding fields

You can use fields to sort data - or use criteria for the fields to filter data - without having to display the field in the query datasheet. Deselect the Show check box (in the design grid) when you don't want to display the column in the datasheet. (The next time you open the query in Design view, you find that Access has moved the hidden field(s) to the right side of the grid. If the field is hidden and not used for sort order or criteria, Access removes it from the grid.)