You can sort records at the server by using a view, stored procedure, user-defined function, or SQL statement in the RecordSource property of a form or report. You sort the records by using the Sort Type grid column or by including an ORDER BY clause in the query's SELECT statement. When you specify a sort order this way, you can perform complex sorts, which means you can sort records in ascending order by some fields and in descending order by others. Each time you view the results of a stored procedure, user-defined function, or SQL statement, the sort order is automatically applied. To sort data when the record source is a table, you must use a local sorting technique.
Sorting records after they are retrieved from the database
You can sort records locally in Form view of a form, in Datasheet view of a table, query, or form, in Page view of a data access page, in PivotChart view, and in PivotTable view. You do this by using the Sort buttons in a form, datasheet, Page, PivotChart, or PivotTable. You can also sort records locally by setting the OrderBy property in form Design view, or by setting the DefaultSort property in Page Design view. For reports, you can sort groups of records locally by using the Sorting and Grouping box, or you can sort on all records locally by setting the report's OrderBy property in Design view.
When you use the Sort buttons, you can perform simple sorts, which means you can sort all records in ascending or descending order (but you can't use a different sort order on more than one field). When you set the OrderBy or DefaultSort property directly, you can perform complex sorts which means you can sort records in ascending order by some fields and in descending order by others. Microsoft Access saves the local sort order that you specify in a form, report, PivotTable, or PivotChart and applies it automatically when you open the object or view. The local sort order for a table, query, or Page is not saved; however, if you create a form or report from the sorted table or query while it's open, the new form or report will inherit the sort order.
Deciding whether to sort before or after database retrieval
The choice of when to sort records can affect performance and even the results of your sort operation. Generally speaking, you should sort records before you retrieve them from the database, because the database is often on a server that is faster than your computer and because SQL Server is designed to sort efficiently. You might want to sort records after retrieving them from the database for ad hoc analysis and when the number of records is relatively small.
Be aware that the results of sorting before you retrieve records from the database might differ from the results of sorting after you retrieve records from the database. This is because the sort order after records are retrieved from the database is determined by the regional settings in Windows Control Panel of your computer, whereas the sort order before records are retrieved from the database is determined by the settings on the computer (defined during SQL Server or SQL Server 2000 Desktop Engine installation) where the database is located.
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.