There are two types of sorts you can do; simple and complex.
-
Simple sorts When you sort in Form view, Datasheet view, or Page view, you do a simple sort, which means you can sort all records in ascending or descending order (but you cannot use both sort orders on more than one field).
-
Complex sorts When you sort in query Design view, the Advanced Filter/Sort window, report Design view, Page Design view, PivotTable view, or PivotChart view, you can perform complex sorts. This means you can sort records in ascending order by some fields and in descending order by others.
How a sort is saved or re-applied depends on the object
-
Saving or re-applying a form, datasheet, report, PivotTable, or PivotChart sort order Regardless of where you specify the sort order, Microsoft Access saves the sort order when you save the form, datasheet, report, PivotTable, or PivotChart. In a form, datasheet, PivotTable, or PivotChart, Access also reapplies the sort automatically when you reopen that object or view, or base a new form or report on a form or datasheet.
-
Saving a data access page sort order In a Page, Access saves the sort order if you create it in Page Design view (by using Data Outline or setting the DefaultSort property), but doesn't save the sort order if you create it in Page view (by using the Sort buttons).
Considerations when sorting records
- If you base a new form or report on a table or query that has a sort order saved with it, the new form or report inherits the sort order.
- Access will sort on up to 255 characters, in one or more fields, in the results of a query or advanced filter.
- Sort order depends on the language setting you specify in the New Database Sort Order box (Options dialog box, General tab) at the time you create your database. If your database contains linked tables from a database that uses a different language sort order, Access uses the sort order of the database that contains the link to the table, not the database where the table is stored.
- If the design grid for the query or filter contains the asterisk from the field list, you can't specify a sort order in the design grid unless you also add the fields you want to sort on to the design grid.
- To sort dates and times from earlier to later, use ascending order. Use descending order to sort from later to earlier.
- Numbers stored in Text fields are sorted as strings of characters, not numeric values. Therefore, to sort them in numeric order, all text strings must be the same length with shorter numbers padded with leading zeros. For example, the result of an ascending sort of the text strings "1", "2", "11", and "22" will be "1", "11", "2", "22". You must pad the single-digit numbers with a leading zero for the strings to be sorted properly: "01", "02", "11", "22". For fields that don't contain Null values, another solution would be to sort on the numeric values of the strings by using the Val function. For example, if the Age column is a Text field that contains numeric values, specifying Val([Age]) in a Field cell and specifying a sort order in its Sort cell will put the records in the correct order. If you are only storing numbers or dates in a Text field, consider changing the field's data type to Number, Currency, or Date/Time in the table where the field is stored. Then, when you sort on the field, the numbers or dates will sort in numeric or date order without requiring leading zeros.
- When you sort a field in ascending order, any records in which that field is blank (contains a Null value) are listed first. If a field contains records with both Null values and zero-length strings, the fields with Null values appear first in the sort order, immediately followed by the zero-length strings.
Sorting on fields with a data type of Memo, Hyperlink, or OLE object
You can't sort an OLE Object under any circumstances. Although you can't sort a Memo or Hyperlink field in a Page, you can sort these fields in a table, query, form, or report. In a report, you must type the name of the Memo or Hyperlink field into the Field/Expression box of the Sorting and Grouping dialog box because those fields don't show up in the list of available fields.
Note Memo fields will only be sorted based on the first 255 characters.
About sorting data in PivotTable or PivotChart view
Sort in ascending or descending order
For example, you can sort a sales column in ascending order or an employee name column in descending order.
You can also sort data separately for each column. The order in which you sort the columns determines how the data will be organized.
In this example, data is organized by salesperson. The Product column was sorted first so that the products would be in alphabetical order; the Salesperson column was sorted second.
When sorting multiple columns, first determine the order in which you want to sort the columns, and then work backward, sorting the outermost column last. For example, if you're sorting two columns, determine which is the outer column and which is the inner column. Sort the inner column first, and then sort the outer column. To list the products in alphabetical order for each salesperson, you would first sort the inner Product column and then sort the outer Salesperson column.
You can define a custom order for a row or column field when sorting in ascending or descending order does not meet your requirements. For example, you might want to show the values in the Title column ordered on the basis of the seniority of the title. If the column had three values
If items are added to a field after you define a custom sort order, the new items will appear at the bottom of the field until you rearrange them.
In a PivotChart view, you can sort numerical data or alphabetical data in ascending or descending order. For example, you can sort a series that contains numerical sales data to show the highest to lowest sales amounts, or you can sort a category field that contains company names to display in ascending order (A - Z).
In the following example, the series field, which consists of sales amounts, is sorted in ascending order to show lowest to highest sales. If the category field had been sorted in ascending order, the salespeople's names would be displayed on the category axis in alphabetical order instead.
If you have a stacked bar, area, or column chart, you can sort numerically based on the height of the entire stacked value rather than just one series.