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

18 Databases – Sorting Data

Learning Objective

Be able to use a single criterion, or multiple criteria, to sort data into ascending or descending order and to evaluate the most appropriate way to sort data in a given situation.

1 Creating a Database Structure (Syllabus 18.1)

1.1 Why a good structure matters

• Ensures data integrity (no duplicate records, correct relationships).
• Facilitates efficient searching, sorting and reporting.
• Provides a clear basis for forms, queries and calculations.

1.2 Flat‑file vs Relational tables

1.3 Field types and sub‑types

1.4 Primary & foreign keys – relationships

• Primary key: a unique, non‑null identifier for each record (e.g., StudentID).
• Foreign key: a field that stores the primary key value from another table, creating a link.
• One‑to‑many example: Students.ClassID → Classes.ClassID. Each student belongs to one class; each class can have many students.
• Enforce referential integrity (Access: Table Design ► Relationships ► Enforce Referential Integrity) to prevent orphaned records.

1.5 Example: “Students” Table

1.6 Importing external data

1. File ► Import ► External Data ► CSV File (or Text).
2. Browse to the file, choose Delimited (comma) or Fixed Width.
3. Map each column to an existing field or let the wizard create new fields.
4. Set the correct data type/sub‑type for each imported column.
5. Check “First Row Contains Field Names” if applicable.
6. Finish – the data appear in a new table ready for relationships.

1.7 Form design (Syllabus 18.1)

A well‑designed form hides the table structure, guides the user and prevents invalid data.

Steps to create a basic data‑entry form

1. Choose Form Wizard ► select the Students table.
2. Drag the required fields onto the layout (typically in logical order).
3. Set StudentID as Locked (auto‑generated).
4. Adjust Tab Order (Design ► Tab Order) so navigation feels natural.
5. Save the form as frmStudents.

Validation checklist (AO2 – Apply)

2 Sorting Fundamentals (AO1 – Recall)

3 Sorting by a Single Criterion

3.1 Procedure (Access, LibreOffice Base, MySQL Workbench, etc.)

1. Select the table or query you wish to sort.
2. Open the Sort dialog (toolbar ► Sort or right‑click ► Sort…).
3. Choose the field (criterion) from the drop‑down list.
4. Select Ascending or Descending.
5. Click OK – the view updates immediately.
6. To keep the order permanently, save the result as a new table or as a saved query.

3.2 Example – Single‑field Sort

Original Students table (unsorted):

Sorted by Score (Descending):

4 Sorting by Multiple Criteria (AO2 – Apply)

4.1 How it works

• The database first sorts on the primary criterion.
• Within each group that shares the same primary value, it sorts on the secondary criterion, and so on.
• This is a stable sort; later criteria do not disturb the order established by earlier ones.

4.2 Procedure

1. Open the Sort dialog (or design a query and use the Sort By row).
2. Enter the first field in the first row and choose its direction.
3. Add a second field in the next row, choose its direction, and continue for any further fields.
4. Run the query or view – the records appear ordered by the hierarchy of criteria.
5. Save the query (e.g., qrySortedProducts) or export the result for later use.

4.3 Example – Two‑field Sort

Original Products table:

Sort by Category (Ascending) then by Price (Descending):

5 Calculations & Queries (Syllabus 18.2)

5.1 Calculated fields

Example: Adding a TotalPrice field to an OrderDetails query.

SELECT ProductName,
Quantity,
UnitPrice,
Quantity * UnitPrice AS TotalPrice
FROM OrderDetails;

The query now shows the line‑item total, which can also be sorted.

5.2 SELECT‑WHERE query with sorting

Show all orders placed in 2024, sorted by OrderDate (Descending) then by CustomerID (Ascending):

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate BETWEEN #01/01/2024# AND #31/12/2024#
ORDER BY OrderDate DESC, CustomerID ASC;

6 Presenting Sorted Data (Syllabus 18.3)

6.1 Creating a simple report

1. Open the sorted query (e.g., qrySortedProducts).
2. Choose Report Wizard ► select the query as the data source.
3. Pick the fields to display.
4. In the wizard’s “Sorting” step, confirm the same order as the query.
5. Choose a layout, add a title, page header/footer and page numbers.
6. Finish and preview the report.

Figure 2 – Report Wizard screenshot (placeholder)

6.2 Exporting sorted results

• In query view, click Export ► CSV (or PDF for a printable report).
• Open the CSV in a text editor to verify that the order matches the query.

7 Key Points & Common Pitfalls (Depth & Accuracy)

7.1 Data‑type considerations

• Numeric vs Text: Text fields sort alphabetically (“10” precedes “2”). Use a numeric field or a calculated numeric expression for true numeric ordering.
• Date/Time fields: Must be stored as Date/Time. Text dates sort lexicographically and give incorrect results.
• Leading zeros: In text fields “001”, “010”, “100” sort as written; in numeric fields they are treated as 1, 10, 100.

7.2 Null / blank handling

Records with Null values are usually placed at the top when sorting Ascending and at the bottom when sorting Descending, but behaviour can vary between DBMSs. Always check the result when blanks are present.

7.3 Sorting stability

A stable sort guarantees that records equal on the first criterion retain the order established by the second (and subsequent) criteria. This matters when you later add another sort level.

7.4 Indexing and performance (AO3 – Evaluate)

• Indexed fields sort faster because the DBMS can read the index in order.
• Creating an index on a large text field increases storage size; weigh benefit against cost.
• For very large tables (e.g., >10 000 records), sorting inside the database is usually quicker than exporting to a spreadsheet, unless the DBMS lacks appropriate indexes.

7.5 Saving a sorted view

• Sorts applied in the table view are temporary – they disappear when the table is reopened.
• To keep the order, save the result as a new table (e.g., tblSortedStudents) or create a saved query that includes an ORDER BY clause.

8 Evaluation Activity (AO3)

Task: Compare sorting a 12 000‑record Orders table directly in Access with sorting the same data after exporting it to Excel.

1. Record the time taken for each method (use a stopwatch).
2. Note any differences in ease of selecting multiple criteria.
3. Discuss the impact of indexing on the Access sort.
4. Write a short paragraph recommending which method to use in a real‑world scenario and justify your choice.

9 Practice Activities (AO2)

1. Open the sample Employees table. Sort by Last Name (Ascending). Record the first and last employee displayed.
2. Using the same table, sort by Department (Ascending) then by Salary (Descending). List the three highest salaries in each department.
3. Create a query that shows all records from the Orders table, sorted by Order Date (Descending) and then by Customer ID (Ascending). Export the result to a CSV file.
4. The field ProductCode is stored as text. After sorting you obtain the order: 1, 10, 2, 20, 3 … Explain why this happens and describe two ways to correct it.
5. Design a simple report based on the sorted query from activity 3. Include a title, page header, and page number.

10 Quick Revision – Match the Term

11 Suggested Flowchart (Figure 3)

A visual flowchart helps learners remember the steps for single‑criterion and multiple‑criterion sorting.

• Open table/query → Choose Sort dialog.
• Single criterion: select field → choose direction → OK.
• Multiple criteria: add first field → set direction → add second field → set direction → OK.
• Save as query or export if a permanent view is required.

Figure 3 – Flowchart placeholder