Data Analysis: Sorting Records

Introduction

One of the actions you can take consists of rearranging the list of records in an order other than the one in which they were entered. For example, a user enters the list of students in the order they arrive. At one time the user may want to see a list of students in alphabetical order based on they last names. Rearranging a list of records is referred to as sorting.

Microsoft Access provides the means of sorting records on all database objects, including tables, queries, and forms. To sort the records of a table, a query, or a form, you can right-click the column or the control that displays its values and click Sort Ascending. This would sort the records based on the field you selected. When a list is sorted in ascending order:

• If the list is made or numeric values, the lowest value would become the first, followed by the second to lowest value, and the highest value would become the last. This is the case for the values in an AutoNumber, a COUNTER, or an AUTOINCREMENT column of a table
• If the list is made of strings (words), the alphabet would be used. The string whose first letter is the highest in the alphabet from a, b, c, etc would be the first. In this case, the ascending order is the same as the alphabetical order. For example, in a list made of Paul, Walter, Anne, and John, in that order, when sorted in ascending order, the list would become Anne, John, Paul, Walter. If various strings in the list start with the same letter, the strings with the same starting letter would be grouped first. Then among the strings with the same starting letter, the second letter would be considered and the same algorithm would be applied
• If the list is made of dates, the earliest date would become the first and the most recent date would become the last
• If the list is a combination of numbers and strings, the numbers would be arranged in incremental order first, followed by the list of strings in alphabetical order
• If the list contains empty values, the empty values would be the first, the other values would be arranged in order depending on their type

To sort the records in the reverse order, you can right-click a column (table or query) or a control (form) and click Sort Descending. After sorting records, if you want to perform another operation, you may need to reset the list to its original arrangement. To do this, you can right-click the object and click Remove Filter/Sort. You can also access these three options on the main menu under Records. They are also available on the toolbar(s).

After sorting the records, you can close the object but you would be asked whether you want to save the arrangement. If you click Yes, the object would be saved with the new arrangement and you can view it next time.

If you are working in a query, to include the arrangement in it, open the query in the Design View. In the lower section of the window, click the Ascending field of the desired column and select Ascending from its combo box. In the same way, you can sort the field in reverse order by selecting the Descending option.

To programmatically sort records on a table, a query, or a form, etc, call its OrderBy() method and pass it the name of the column on which the sorting would be based. After calling OrderBy(), access its OrderByOn Boolean property and set its value to True. To remove the sorting, access the OrderByOn and set its value to False.

Sorting Records on a Query

If you are visually creating a query using the Select Query window, to sort the records, after selecting the column on which the sorting would be based, click the box corresponding to its Sort row, click the arrow of its combo box and select Ascending:

To sort the records in reverse order, select Descending instead.

Sorting Records With SQL

In the SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The syntax used would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The field used as the basis must be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named StaffMembers. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:

SELECT FirstName, LastName FROM StaffMembers 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, as we learned during data analysis. Imagine that you have created a query that includes all fields. The following statement would list the records of the StaffMembers table based on the alphabetical order of the LastName column:

SELECT * FROM StaffMembers ORDER BY LastName;

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName ASC;

The second statement can be written as:

SELECT * FROM StaffMembers 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. To sort records in reverse alphabetical order, the above two statements can be written as:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM StaffMembers ORDER BY LastName DESC;

If you want to programmatically create a query from one of these statements, remember that you can use the CreateQueryDef() method.

Practical Learning: Sorting Records on a Form

1. In the Forms section of the Database window, right-click Students and click Design View
2. Display the Form Footer section. On the Toolbox, click the Combo Box and click the left side under the Form Footer bar. If the Combo Box wizard starts, click Cancel
3. In the Properties window, change the name of the combo box to cboColumnNames1
4. Set its Row Source Type to Value List
5. Change the Caption of its accompanying label to Sort by:
6. Make sure the Control Wizard button of the Toolbox is down. On the Toolbox, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
7. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
8. Click under Col1 and type Ascending Order
9. Press the down arrow key and type Descending Order
10. Click Next
11. In the third page of the wizard, accept the first radio button and click Next
12. Change the label to in and click Finish
13. In the Properties window, change the combo box' Name to cboSortOrder
14. Access the properties of the form by double-clicking the button at the intersection of the rulers and click the Events tab
15. Double-click On Open to select [Event Procedure], then click its ellipsis button
16. Implement the event as follows:
     

    Private Sub Form_Open(Cancel As Integer) On Error GoTo FormOpen_Err Dim curDatabase As Object Dim strColumnsNames As String Dim tblStudents As Object Dim fldColumn As Object ' Get a reference to the current database Set curDatabase = CurrentDb ' Get a reference to a table named Students Set tblStudents = curDatabase.TableDefs("Students") ' Retrieve the name of each column of the table and ' store each name in the strColumnsNames string For Each fldColumn In tblStudents.Fields strColumnsNames = strColumnsNames & fldColumn.Name & ";" Next ' Set the strColumnsNames string as the data source of the combo box cboColumnNames1.RowSource = strColumnsNames ' Select the name of the first column as the default of the combo box cboColumnNames1 = tblStudents.Fields(0).Name cboOrderBy = "Ascending Order" Exit Sub FormOpen_Err: MsgBox "There was an error when opening the form." & vbCrLf & _ "Error #: " & Err.Number & vbCrLf & _ "Description: " & Err.Description & vbCrLf & _ "Please contact the program vendor if " & _ "he is not sleeping at this time." Resume Next End Sub

17. Return to the form and click the Sort By combo box
18. In the Events section of the Properties window, double-click After Update
19. Click its ellipsis button and implement the event as follows:
     

    Private Sub cboColumnNames1_AfterUpdate() Dim strColumnName As String Dim strSortOrder As String ' Get the name of the column from the Sort By combo box strColumnName = cboColumnNames1 ' Sort the records based on the column name from the combo box Me.OrderBy = strColumnName Me.OrderByOn = True ' Set the In combo box to ascending order by default cboSortOrder = "Ascending Order" End Sub

20. Return to the form and click the in combo box
21. In the Events section of the Properties window, double-click After Update
22. Click its ellipsis button and implement the event as follows:
     

    Private Sub cboSortOrder_AfterUpdate() Dim strColumnName As String Dim strSortOrder As String strColumnName = cboColumnNames1 ' Unless the user selects Descending Order... If cboSortOrder = "Descending Order" Then strSortOrder = "DESC" Else ' We will consider that it should be sorted in ascending order strSortOrder = "ASC" End If Me.OrderBy = strColumnName & " " & strSortOrder Me.OrderByOn = True End Sub

23. Return to the form and, from the Toolbox, add a Command Button to the right side Form Footer section
24. When the Command Button Wizard starts, click Cancel
25. Change the button Name to cmdRemoveSort and set its Caption to
    Remove Sort
26. While the button is still selected, in the Properties window, double click On Click
27. Click its ellipsis button and implement the event as follows:
     

    Private Sub cmdRemoveFilterSort_Click() Me.OrderBy = "StudentID" Me.OrderByOn = True Me.cboColumnNames1 = "StudentID" Me.cboSortOrder = "Ascending Order" End Sub

28. Return to the form and save it
29. Using the combo box in the Form Footer section, try sorting the records by the LastName is descending order
     
    
30. Remove the sort order
31. Try sorting the records in ascending order based on the date of birth
     
    
32. Notice that the records with no date of birth appear first
33. Close the form