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 Employees. 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 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, 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 Employees table based on the alphabetical order of the LastName column:

SELECT * FROM Employees 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 Employees ORDER BY LastName ASC;

The second statement can be written as:

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

SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM Employees 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.

If you create a SQL expression what involves more than one table, you can use one of the fields to sort the records. To do this, after the join expression, add your sort clause. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "ORDER BY Employees.LastName;"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

Of course, you can use any column in the SQL statement. Here is an example that uses a column of the second table:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "ORDER BY Departments.Department;"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub