A Sort Order allows you to sort on several fields at once. In addition, you can save a sort order and re-apply it at will.
To create a sort order for a table, first go into the table and then follow these steps:
Click on the Sort Order button on the Query toolbar (or select Sort Order from the Format menu). The "Format Query" dialog is opened at the Sort Order page. All the fields of the table are initially listed in the Available Fields box. You need to select the fields you wish to sort on - and in the right order, to determine the priority for each of the sort fields.
Select the first field by clicking on it and then click on Add. The field will be moved to the Selected Fields box.
Select the next field and again click on Add to position it after the first field.
Repeat the operation till all the fields you wish to sort on have been moved to Selected Fields. The first field has priority one; the second field priority two; and so on.
If you decide that you don't want to sort on a particular field or you have given a field the wrong priority, just click on the field in Selected Fields and click on Remove.
The sort proceeds in this way:
First sort: the first field is sorted - this is the main sort, with priority one.
Second sort: the second field is sorted, but only if there were duplicate values in the first field i.e. the second field is sorted within the groups determined by the duplicate values in the first field.
Third sort - the third field is sorted, but only if there were duplicate values within the groups in the second; and so on.
Note: This has the consequence that if your table has a primary key or a unique index (or indexes) i.e. no duplicate values are allowed, and this is the first field to which a sort is applied, then sorting on the other fields can have no effect, no matter what type of indexing they have. The basic rule is that it makes sense to sort up to a primary key or unique index field, but not beyond. So if the first two fields, say, in a sort order are not indexed or have a non-unique index, and the third has a primary key or unique index, you would sort up to the third field but not beyond.
Once you have decided which fields to use in the sort order and the order of sorting, you can determine whether each of the fields is to be sorted in ascending or descending order. To do this click on each of the fields in turn and click on the Descending checkbox to impose descending order. The default is ascending, which is indicated by an unchecked Descending checkbox. Ascending order is A…Z, 1…n, descending order Z…A, n…1, as you would expect.
Give the sort order a name in the Name box and click on OK. You will be asked if you want to create a new sort order. Click Yes to save the sort order under the name you have given it.
You are returned to the Table Grid View with the sort order applied. The name of the sort order is displayed in the Sort Order box on the Query toolbar. Whenever you need to apply this sort order in future, just click on the arrow in the Sort Order box and select the sort order from the drop-down list.
Note: If you don't give a name to the sort order, the sort order you have defined will still be applied but it won't be saved for future use. In this case the Sort Order box on the Query bar will say Sort Order - Untitled, which is the title given to the last applied unsaved sort.