Sorting records in an Access project

Applies to
Microsoft Access 2003
Microsoft Access 2000 and 2002

You can sort records in database objects -- such as datasheets, forms, reports, and data access pages -- in an Access project just as you can in an Access database. As in an Access database, you can perform both simple and complex sorts in an Access project. In a simple sort, you sort all records in ascending or descending order (but not both). In a complex sort, you sort records by more than one column, and, if desired, in ascending order by some columns and in descending order by others.

The Employee Sales by Country stored procedure sorted by last name and sales amount

However, in some ways, sorting records in an Access project is different from sorting records in an Access database. The key difference is that in an Access project, you can sort records either before or after they are retrieved from the database, which is either a Microsoft SQL Server™ database or Microsoft Data Engine (MSDE).

Deciding when to sort

Whether you sort the records before or after they are retrieved depends on the database object you are working with and the method of sorting you use. When you sort can affect the performance and even the results of the sort operation.

When you 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 typically on a server that is faster than your machine and because Microsoft SQL Server™ and MSDE are 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 MSDE installation) where the database is located.

Sorting database records before they are retrieved in Access

There are two ways to sort records before they are retrieved from the database:

When you specify a sort order before records are retrieved from the database, you can perform both simple and complex sorts. Each time you view the results of a stored procedure or SQL statement, the sort order is automatically applied.

Sorting database records after they are retrieved in Access

You can sort records after they are retrieved from the database in a table, view, stored procedure, data access page, form, or report. In fact, this is the only way to sort a table or view. There are several ways to sort records that have already been retrieved from a database:

  • To perform simple sorts, use the Sort buttons on the toolbar (Sort Ascending or Sort Descending) for tables, views, stored procedures, forms, and pages. For tables and views, you can perform simple sorts only.
  • Set the OrderBy property of a form or report.
  • Use the Sorting and Grouping dialog box (View menu) to sort groups of records in reports and pages.

When you sort a form or report by using the Sort buttons, Access saves the sort in the OrderBy property and automatically applies it when you open the form or report. When you set the OrderBy property in the property sheet of a form or report, or the DefaultSort property in the Sorting and Grouping dialog box for a page, Access saves the sort order for the object in the relevant property.

When you sort a page, stored procedure, table, or view by using the Sort buttons, Access does not save the sort order. However, when you create a form or report from a sorted table or view while it's open (for example, by using AutoForm or AutoReport), the new form or report inherits the sort order of the underlying table or view.

About sorting data in PivotTable or PivotChart view

Sorting data in PivotTable or PivotChart view is slightly different from sorting data in other views.

ShowIn PivotTable view

ShowSort 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.

Example of data sorted by two columns

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.

ShowDefine a custom sort order

You can define a custom order for a row (row field: A field in the row area of PivotTable view. Items in row fields are listed down the left side of the view. Inner row fields are closest to the detail area; outer row fields are to the left of the inner row fields.) or column field (column field: A field in the column area of PivotTable view. Items in column fields are listed across the top of a PivotTable list. Inner column fields are closest to the detail area; outer column fields are displayed above the inner column fields.) 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 — Vice President, General Manager, and Manager — sorting in ascending or descending order will not show the data in the order you want. In this case, you might want to explicitly define the order of the items for the Title column.

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.

ShowIn PivotChart view

In a PivotChart view, you can sort numerical data or alphabetical data in ascending or descending order. For example, you can sort a series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart.) that contains numerical sales data to show the highest to lowest sales amounts, or you can sort a category field (category field: A field that is displayed in the category area of PivotChart view. Items in a category field appear as labels on the category axis.) that contains company names to display in ascending order (A - Z).

In the following example, the series field (series field: A field that is displayed in the series area of a chart and that contains series items. A series is a group of related data points.), 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.

Chart with series sorted in ascending oder

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.

Step-by-step procedures for sorting records

For step-by-step information, see the topic Sort records (ADP).

Remove a local sort

In Form view or Datasheet view, click Remove Filter/Sort on the Records menu.

Troubleshoot sorting records in a project

Some of the content in this section may not be applicable to some languages.

ShowI applied a local sort order and now it looks like some records are missing.

The maximum number of records (maximum record limit: To improve performance, you can specify the maximum number of records that will be retrieved from a Microsoft SQL Server database for a form or datasheet in an Access project.) determines how many records can be retrieved from the database. If the limit is too low, you aren't sorting on a complete set of records. If you need to work with all the records, raise the limit. If you don't need to work with all the records in the database, create a server filter to limit records to the subset you need.

ShowI receive replication errors after I change the language sort order.

If you're synchronizing between replicas in a Microsoft SQL Server database or SQL Server 2000 Desktop Engine, you might encounter synchronization conflicts if the databases don't have the same language sort orders.

About working around table and view sort limitations in Access

Although sorting tables and views is limited compared to other database objects, you can work around these limitations by saving the table or view as another object and then sorting the records. For example, you can:

  • Save the table or view as a form datasheet and then sort the datasheet.

ShowHow?

  1. In the Database window, click Tables or Views under Objects, and then select the table or view you want to save as a form datasheet.
  2. Click the arrow next to the New Object button on the toolbar, and then click AutoForm to quickly create a form.
  3. Switch to Design view.
  4. Make sure the form is selected and then click the Properties button.
  5. Click the Format tab, and then set the DefaultView property to Datasheet.
  6. Switch to Datasheet view.
  7. Do one of the following:
    • To sort in ascending order, click the Sort Ascending button.
    • To sort in descending order, click the Sort Descending button.
  8. Save your form datasheet.
  • Save the table or view as a stored procedure with an ORDER BY clause.

ShowHow?

If the object you want to save is a view, go to step 5. If the object is a table, do the following:

  1. In the Database window, click Tables under Objects, and then select the table you want to save as a view.
  2. Click the arrow next to the New Object button on the toolbar and then click View to create a view in the Query Designer.
  3. Select all the columns in the field list.
  4. Save your view.
  5. Copy the SQL statement from the SQL pane of the Query Designer, paste it into a stored procedure, and add an ORDER BY clause. To see a demonstration, click Play Demo, and then click the Start button:
    Play Demo
Summary of sort operations in Access

The following table summarizes sort operations in an Access project.

Database object Sort records before database retrieval Save the sort before database retrieval Sort records after database retrieval Save the sort after database retrieval
Stored procedure Yes Yes
(by adding an ORDER BY clause)		 Yes
(simple sorts only)		 No
SQL statement
(in the RecordSource property of a form or report)		 Yes Yes
(by saving the form or report that contains it)		 Not applicable Not applicable
Form and report Yes
(when the record source is an SQL statement or stored procedure)		 Yes
(when the record source is an SQL statement or stored procedure)		 Yes
(by using the Sort buttons or by setting the OrderBy property)		 Yes
Page
(see Note)		 Yes
(when the record source is a stored procedure)		 Yes
(when the record source is a stored procedure)		 Yes
(by using the Sort buttons or by setting the DefaultSort property)

No
(by using the Sort buttons)

Yes
(by setting the DefaultSort property)
Table and view No No Yes
(simple sorts only)		 No

 Note    The sort behavior also applies to data access pages in an Access database (which uses the Microsoft Jet database engine), except that you use queries instead of stored procedures.