Home

Sorting Records

Fundamentals of Sorting Records

Introduction

Sorting records consists of rearranging them in alphabetical, incremental, chronological, or Boolean order. Sorting can be performed on fields of any type of value. Fields also may not display any value but can be sorted too.

Practical Learning: Introducing Sorting Records

  1. Start Microsoft Access
  2. In the list of files, click States Statistics2 from the previous lesson
  3. In the Navigation Pane, double-click the States table
  4. In the Text Formatting section of the Ribbon, click the arrow of the Font Color button and select the white color

Sorting Records in the Regular View

There are two categorical ways to produce sorted records. To sort records while you are viewing them, display the table or the query in Datasheet View, the form in Form View, or the report in Layout View. Then use the options in the Sort & Filter section of the Ribbon.

To sort records, you must first decide what field will be used as the reference. To arrange the list in alphabetical, incremental, chronological, or Boolean order:

  • On the table or query, a form displaying in the Datasheet View:
    • Click the down-pointing button on the right side of the column header:

      Filter

      and click Sort A to Z
    • Click a cell in the column of your choice. In the Sort & Filter section of the Ribbon, click Ascending Descending

      Form - Sorting Records

    • Right-click a cell in the column of your choice and click Sort A to Z

      Sorting Records

      Sorting Records

  • On a form in Form View or in a tabular format, or a report in Layout View:
    • Click the text box or the control (such as a combo box) of your choice. In the Sort & Filter section of the Ribbon, click Ascending Descending
    • Right-click the text box or the control (such as a combo box) of your choice and click Sort A to Z

      Report - Sorting Records

      Report - Sorting Records

Practical Learning: Sorting Records

  • Click the arrow on the right side of Capital and click Sort A to Z

    Form - Sorting Records

Unsorting the Records

When you have finished sorting the records, you may want to reset the list before continuing with the next operation. To assis you with this, the Sort & Filter section of the Ribbon is equipped with the Remove Sort button Clear All Sorts.

To put the list back in the sequence it previously had:

  • In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts

    Table - Sorting Records

  • Right-click the query or form and click Remove Sort

Practical Learning: Unsorting Records

  • In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts

Sorting Records in Descending Order

Besides the regular arrangement of records, you can also sort records in reverse alphabetical, incremental, or chronological. To do this:

  • On the table, query, or form in Datasheet View:
    • Click the down-pointing button on the right side of the column header and click Sort Z to A
    • Click a cell in the column of your choice. In the Sort & Filter section of the Ribbon, click Descending Descending

      Record Sorting

    • Right-click a cell in the column of your choice and click Sort Z to A
  • On the form in a view other that Design View or a report in Layout View:
    • Click the text box or the control (such as a combo box) of your choice. In the Sort & Filter section of the Ribbon, click Descending Descending
    • Right-click the text box or the control (such as a combo box) of your choice and click Sort Z to A

Record Sorting

Sorting Records by Design

If you include the sorting operation in the design of a table, you must save the table before viewing the results. If you save the table like that, the sorting feature would be included as belonging to the design of the table.

To prepare a table for record sorting in design:

  • Display the table in Design View. In the Property Sheet of the table, click the Order By field. Type the name of the field that will be used as reference. Here is an example:

    Ceil Inn - Sorting the Records of a Table

    You must first save the table before displaying it in the Datasheet View to see the result
  • Display the query in Design View and select the field that will be used as reference. In the bottom side of the window, click the combo box that corresponds to the Sort row of the field and select Ascending
  • Display the form or the report in Design View:
    • If the Record Source is set to a table or query, access the Property Sheet (that of the form or report). In the Data or the All tab of the Property Sheet of the form, click the Order By field. Type the name of the field (or control) that will be used as reference
    • If the Record Source is a SQL statement, click the ellipsis button Browse of the Record Source. In the bottom side of the window, click the combo box that corresponds to the Sort row of the field and select Ascending

      Query = Sorting Records

 
 
 

Primary Topics on Sorting Records

Sorting Records in SQL

In SQL, to indicate that you want to sort records, include the ORDER BY expression at the end of the SELECT statement. This expression must be followed by the name of the column used as reference. The formula to follow is:

SELECT what FROM what-object ORDER BY what-field;

The field used as the basis should be recognized as part of the selected columns. Here is an example:

SELECT ItemName, UnitPrice FROM StoreItems ORDER BY ItemName;

Remember that you can spread a SQL statement to many lines. In this case, you can put the ORDER BY clause on its own line. Here is an example:

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName;

If you use the * operator to include all fields, you can order the list based on any of the table's fields. Here is an example:

SELECT * FROM Employees ORDER BY LastName;

As mentioned already, by default, records are ordered in ascending order. Nevertheless, the ascending order is controlled by the ASC keyword specified after the based field. Here is example:

SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;

If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. Here is an example:

SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC;

The second statement can be written as follows:

SELECT * FROM Employees ORDER BY LastName DESC;

Programmatically Sorting Records

To let you programmatically sort records, the Form class is equipped with a property named OrderBy. To sort the records of a form, assign the desired field to this property. To let you apply the sorting, the Form class is equipped with a Boolean property named OrderBy. Therefore, after using the OrderBy property, set the OrderByOn property to True. Here is an example:

Private Sub cmdSortByLastName_Click()
    OrderBy = "LastName"
    OrderByOn = True
End Sub

To remove the sorting, access the OrderByOn and set its value to False.

Sorting Records Based on Type

Record Sorting and Null Fields

When some values are not available, a table, a query, a form, or a report may display empty fields. These are also referred to as null fields or null values. When sorting records in alphabetical, incremental, or chronological order, the empty fields always come first.

Sorting Number-Based Fields

The columns that use the numeric values can be sorted in incremental order. To visually sort records based on a number-based column:

  • Display the table, the query, or the form in the Datasheet View. Click the down-pointing button on the right side of the column header and click Sort Smallest to Largest
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Right-click a number-based field or text box and click Sort Smallest to Largest
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Click a number-based field or text box. In the Sort & Filter section of the Ribbon, click the Ascending button Descending

In the same way, you can sort the records in the reverse order. Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.

Sorting Boolean Fields

Boolean fields are those that use False and True values. To sort records based on a Boolean field:

  • Display the table, the query, or the form in the Datasheet View. Click the down-pointing button on the right side of the column header and click Sort Selected to Cleared
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Right-click a check-based field and click Sort Selected to Cleared
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Click a check-based field. In the Sort & Filter section of the Ribbon, click the Ascending button Descending

In the same way, you can sort the records in the reverse order. Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.

Sorting More Than One Column

Introduction

Imagine you arrange records based on a certain column that has repeating values:

ELECT Region, 
      StateName,
      Capital,
      AreaSqrMiles, 
      AdmissionUnionOrder
FROM States
ORDER BY Region;

If you get a situation where many records on a column have the same value, you can specify an additional column by which to sort the records. To visually sort more than one column, in the Design View of a query, select the Sort value of each field. Here is an example:

Sorting Records

Query = Sorting Records on Many Fields

To arrange the list using more than one column using the SQL, after ORDER BY, type the columns separated by commas. Here is an example:

SELECT Region, 
       StateName,
       Capital,
       AreaSqrMiles, 
       AdmissionUnionOrder
FROM States
ORDER BY Region, StateName;

Sorting Null and Non-Null Fields

If you specify more than one record to sort by, the database engine sorts the primary column first. Then, on the second field, when two records have the same value, the NULL values would come first.

Sorting Many Number-Based Fields

If you have two integer-based fields that have repeating values, if you sort the list based on the first field, the records with similar values in the first field would be grouped. If the records are sorted in the second field, they would be sorted for each group of similar first field records.

Options on Sorting Records

Sorting by an Expression

When sorting the records, the database engine mostly needs to have a value as reference, the value by which to arrange the records. Based on this, besides, or inside of, (a) column(s), you can use an expression to sort the records. To do this visually, in the Design View of a query, in place of a Field name, type the expression. Here are examples:

Sorting Records

Query = Sorting Records Using an Expression

Using a Function to Sort Records

Just as you use an expression as a basis for sorting records, you can use the return value of a function to arrange records.

Practical Learning: Ending the Lesson

  • Close Microsoft Access.
    If you are asked whether you want to save something, click No
 
 
   
 

Previous Copyright © 2002-2016, FunctionX, Inc. Next