Home

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 LearningPractical 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
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next