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
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 .
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:
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:
You must first save the table before displaying it in the Datasheet View to see the result
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:
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:
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 |
|