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:

Practical Learning: 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:

Practical Learning: Unsorting Records

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:

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:

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:

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:

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


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