Be able to use a single criterion, or multiple criteria to sort data into ascending or descending order

18 Databases

Objective

Be able to use a single criterion, or multiple criteria to sort data into ascending or descending order.

What is Sorting?

Sorting arranges the records in a table so that they appear in a particular order. The order can be:

• Ascending – from smallest to largest, A to Z, earliest to latest.
• Descending – from largest to smallest, Z to A, latest to earliest.

Why Sort Data?

• Find information quickly (e.g., the highest sales figure).
• Prepare data for printing or reporting.
• Identify duplicates or out‑of‑range values.
• Make it easier to compare records.

Sorting by a Single Criterion

A single criterion means you choose one field (column) to determine the order.

1. Select the table or query you wish to sort.
2. Locate the Sort option (often in the toolbar or right‑click menu).
3. Choose the field you want to sort by.
4. Select Ascending or Descending.
5. Apply the sort – the records are now displayed in the chosen order.

Example – Single‑Field Sort

Consider the following table of student scores:

Sorting by Score in Descending order gives:

Sorting by Multiple Criteria

When two or more fields are used, the database sorts by the first field, then within each group it sorts by the second field, and so on.

1. Open the Sort dialog (or use the query design grid).
2. Enter the first field in the Sort By column and choose the direction.
3. Enter the second field in the next row and choose its direction.
4. Continue for additional fields if required.
5. Run the sort – records are ordered by the hierarchy of criteria.

Example – Two‑Field Sort

Suppose we have a product inventory table:

Sort first by Category (Ascending) and then by Price (Descending). The result:

Key Points to Remember

• Ascending order is the default for alphabetical and numeric fields.
• When sorting dates, the earliest date is first in ascending order.
• Multiple‑criteria sorting is useful for hierarchical data (e.g., Country → City → Postal Code).
• Sorting does not change the underlying data; it only changes the view unless you save the sorted query as a new table.
• Be aware of data types – mixing text and numbers in the same field can give unexpected results.

Common Pitfalls

• Sorting a text field that contains numbers will sort alphabetically (e.g., “10” comes before “2”). Convert the field to a numeric type if numeric sorting is required.
• Leading spaces in text fields affect order – clean data before sorting.
• Sorting on a field that contains null (blank) values may place those records at the top or bottom depending on the software.

Practice Activities

1. Open the sample “Employees” table. Sort the records by Last Name in ascending order. Note the first and last records.
2. Using the same table, sort by Department (Ascending) and then by Salary (Descending). List the top three salaries in each department.
3. Create a query that shows all orders from the “Orders” table, sorted by Order Date (Descending) and then by Customer ID (Ascending). Export the result to a CS \cdot file.
4. Explain why sorting the “Product Code” field (stored as text) gave an unexpected order, and describe how to correct it.