Lesson 12	Specifying a sort order
Objective	Refine your query by specifying a sort order.

Refine Query by Specifying Sort Order

When you create a query, you may find that the records come out in the order you want them. However, if they are not in the desired order, one option is to use the Sort buttons on the datasheet to sort them. If you are going to use the query to create a form or report, however, or if you want to see it sorted as soon as you view the datasheet, you should specify the sort order as part of the query definition. To specify the sort order, use the Sort row in the query grid. When you place the cursor in one of the cells in the Sort row, you see a drop-down arrow. You can choose Ascending, Descending or Not Sorted to specify the sort order. When you specify a sort order for more than one field Access works from left to right: The field furthest to the left in the grid that has a sort order specified will be the primary sort key, any other sorted fields will be secondary. We will have a look at this in the following Simulation.
Add a calculated field to the query in the next lesson.

Specify Query Sort Order

This datasheet is the result of a query. It seems to be sorted by date. However, you'd like to sort it by Company.
Click the View button to see the query in Design view.
To sort by Company you need to set a sort order in the Sort row for the Company field. Click the Sort row in the Company column to see the arrow for the drop-down list.
Display the drop-down list by clicking the arrow and choose Ascending. Ascending sorts text data from the beginning of the alphabet to the end, date data from the earliest date to the latest, and number data from the smallest to the largest.
Now that you have defined the sort order, have another look at the datasheet by clicking the View button on the toolbar.
Although you can only see one company listed (except for the last record on the screen) the records do appear (and, in fact, are) to be sorted by company. However, the records for ABC Webworks, although they all appear at the top of the datasheet, seem to be in no particular order. You can specify a secondary sort field to put records with the same value in the primary sort field (in this case, all the records for ABC Webworks) in a particular order. Click the View button to return to Design view to further define the sort order for the query.
Set Project ID as the secondary sort field. That way all the work for one project will appear together. Click the Sort row in the Project ID column to see the arrow for the drop-down list.
Display the drop-down list and choose Ascending.
Now view the datasheet again by clicking the View button.
Here you see that now in addition to sorting by company the datasheet is also sorted by project ID. If you want, you can also sort by date--but remember that Access works from left to right when sorting, so it would sort first by Company, then by Project ID, then by Date because that is the order the fields appear in the query design.

Custom Sort Order for Displaying Records

I wrote an application one time where I tracked the work I did over the course of a week. This database also had a feature where I could send my manager an e-mail that displayed these items under category headings.
For example, customer-related activities such as responding to questions or attending user group meetings were included under my Customer heading, while coaching or mentoring were included under a heading called People Management. This database worked well for me, and for my headings, but I wanted to make it more generic. So, the database was also able to customize the category headings, as well as the order in which they appeared in the e-mail. One person might choose to show the People Management category at the top of the e-mail, while another might choose to show Customer activity at the top.

Specifying Sort Order - Exercise

Practice refining your query by defining a sort order.
Specifying Sort Order - Exercise