Sorting Query Results in MS Access

Sorting organizes records according to the values in one or more fields. For example, you can arrange student names from A to Z or fees from highest to lowest.

Example: Students Table

Sorting these records by StudentName in ascending order would display Amit, Neha, Priya, and Rahul.

Ascending Order

Ascending order arranges values from smallest to largest or alphabetically from A to Z.

• Names: A to Z
• Numbers: Smallest to largest
• Dates: Oldest to newest

Descending Order

Descending order arranges values from largest to smallest or alphabetically from Z to A.

• Names: Z to A
• Numbers: Largest to smallest
• Dates: Newest to oldest

In MS Access SQL, the ORDER BY clause specifies how the query results should be sorted.

Example 1: Sort Students by Name

SELECT *
FROM Students
ORDER BY StudentName ASC;

This query displays students alphabetically by name.

Example 2: Sort Students by Fees

SELECT StudentName, Fees
FROM Students
ORDER BY Fees DESC;

This query displays students with the highest fees first.

Example 3: Sort by Admission Date

SELECT StudentName, AdmissionDate
FROM Students
ORDER BY AdmissionDate ASC;

This query displays students from the earliest admission date to the latest.

Follow these steps to sort records in Design View:

1. Open your MS Access database.
2. Click the Create tab.
3. Select Query Design.
4. Add the Students table.
5. Add StudentName, Course, and Fees to the query grid.
6. Locate the Sort row under the StudentName field.
7. Select Ascending or Descending.
8. Click Run (!).
9. Save the query with a suitable name, such as qryStudentsSortedByName.

Example of Query Design Settings

The query sorts by StudentName while displaying the other selected fields.

You can sort records using more than one field. Access applies the primary sort first and uses the next sort field to order records that have equal primary values.

Example: Sort by Course and Then Name

SELECT StudentName, Course, Fees
FROM Students
ORDER BY Course ASC, StudentName ASC;

The query arranges courses alphabetically. Within each course, student names are also arranged alphabetically.

Example: Sort by Course and Highest Fees

SELECT StudentName, Course, Fees
FROM Students
ORDER BY Course ASC, Fees DESC;

This query groups the output order by course name and places students with higher fees first within each course.

You can combine sorting with filtering criteria. The WHERE clause filters records, while ORDER BY arranges the remaining results.

Example: Find ADCA Students and Sort by Name

SELECT StudentName, Course, Fees
FROM Students
WHERE Course = "ADCA"
ORDER BY StudentName ASC;

This query displays only ADCA students and sorts them alphabetically by name.

Example: Find Students with Fees Above 5000

SELECT StudentName, Fees
FROM Students
WHERE Fees > 5000
ORDER BY Fees DESC;

This query displays students whose fees exceed 5000, starting with the highest fee.

A field may contain Null values, meaning the value is missing or unknown. When you sort a query, Access places Null values according to its sorting rules and the field type.

Example: Sort Students by Admission Date

SELECT StudentName, AdmissionDate
FROM Students
ORDER BY AdmissionDate ASC;

If some students do not have an AdmissionDate, their records may appear together at one end of the sorted results. The exact position depends on the sort order and Access behavior.

Exclude Records with Missing Dates

SELECT StudentName, AdmissionDate
FROM Students
WHERE AdmissionDate IS NOT NULL
ORDER BY AdmissionDate ASC;

This query excludes records without an admission date and sorts the remaining records from oldest to newest.

• Makes records easier to read and understand.
• Helps identify the highest or lowest values.
• Organizes student names alphabetically.
• Makes date-based reports easier to review.
• Allows sorting by multiple fields.
• Works together with query criteria.

Sorting changes the order of the query results. It does not automatically change the order in which records are stored in the underlying table.

Create a Students table with StudentID, StudentName, Course, Fees, and AdmissionDate fields. Add at least ten sample records.

Tasks

1. Create a query to sort students by name in ascending order.
2. Create a query to sort students by name in descending order.
3. Display students from highest fees to lowest fees.
4. Sort students by admission date from oldest to newest.
5. Sort students by course and then by name.
6. Sort students by course and then by fees in descending order.
7. Display only ADCA students in alphabetical order.
8. Find students with fees above 5000 and sort by fees.
9. Create a query that excludes records with missing admission dates.
10. Save each query with a meaningful name.

Goal: Learn to sort query results using Design View and SQL View.

1. What is sorting in MS Access?
2. What is the difference between ascending and descending order?
3. Which SQL clause sorts query results?
4. What is the purpose of ASC and DESC?
5. How can you sort records using multiple fields?
6. Can you use WHERE and ORDER BY in the same query?
7. Does sorting a query permanently rearrange records in the table?
8. How can you exclude records with Null admission dates?