Select Queries in MS Access

A Select query is a query used to retrieve records that meet specific requirements. It is one of the most frequently used query types in Microsoft Access.

Example: Students Table

A Select query can display all students, show only student names, or find students enrolled in ADCA.

1. Open your Microsoft Access database.
2. Click the Create tab on the ribbon.
3. Select Query Design.
4. Add the Students table to the query design window.
5. Close the Add Tables window.
6. Double-click the fields you want to display.
7. Add criteria or sorting options if required.
8. Click Run (!) to view the results.
9. Press Ctrl + S to save the query.
10. Enter a meaningful query name, such as qryStudents.

Use the asterisk (*) to retrieve all fields from a table.

SELECT *
FROM Students;

Explanation

• SELECT specifies the data to retrieve.
• * means all fields.
• FROM Students specifies the source table.
• The semicolon marks the end of the SQL statement.

This query displays all fields and records in the Students table.

You do not always need to display every field. You can select only the fields required for a report.

Example

SELECT StudentName, Course
FROM Students;

This query displays only StudentName and Course. StudentID and Fees are not included in the results.

Select Three Fields

SELECT StudentID, StudentName, Fees
FROM Students;

This query displays the student ID, student name, and fees fields.

The WHERE clause filters records according to a specified condition.

Example 1: Find ADCA Students

SELECT *
FROM Students
WHERE Course = "ADCA";

This query displays only students whose course is ADCA.

Example 2: Find Students with Fees Above 5000

SELECT StudentName, Fees
FROM Students
WHERE Fees > 5000;

This query displays the names and fees of students whose fees are greater than 5000.

Example 3: Find Students with Fees Below 10000

SELECT StudentName, Fees
FROM Students
WHERE Fees < 10000;

This query returns students whose fees are less than 10000.

Using AND

AND requires both conditions to be true.

SELECT *
FROM Students
WHERE Course = "ADCA"
AND Fees > 5000;

This query finds students whose course is ADCA and whose fees are greater than 5000.

Using OR

OR returns records that satisfy at least one of the specified conditions.

SELECT *
FROM Students
WHERE Course = "ADCA"
OR Course = "Tally Prime";

This query displays students enrolled in ADCA or Tally Prime.

The ORDER BY clause arranges query results in ascending or descending order.

Ascending Order

SELECT StudentName, Fees
FROM Students
ORDER BY StudentName ASC;

This query arranges student names alphabetically. ASC means ascending order and is the default sort direction.

Descending Order

SELECT StudentName, Fees
FROM Students
ORDER BY Fees DESC;

This query displays students with the highest fees first. DESC means descending order.

Sort by Multiple Fields

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

This query sorts students by course and then alphabetically by student name within each course.

The DISTINCT keyword returns only unique combinations of the selected fields.

Example

SELECT DISTINCT Course
FROM Students;

If many students are enrolled in ADCA, this query displays ADCA only once in the results.

If you select more than one field with DISTINCT, Access returns unique combinations of those fields.

A Select query can retrieve related information from more than one table when the tables are connected through appropriate fields.

Example Tables

Students Table

• StudentID
• StudentName
• CourseID

Courses Table

• CourseID
• CourseName
• CourseFees

SQL Query

SELECT Students.StudentName,
       Courses.CourseName
FROM Courses
INNER JOIN Students
ON Courses.CourseID = Students.CourseID;

This query combines related records from both tables and displays each student's name with the associated course name.

An INNER JOIN returns records where the join condition matches in both tables.

Create a table named Students with these fields:

• StudentID — AutoNumber
• StudentName — Short Text
• Course — Short Text
• Fees — Currency

Tasks

1. Enter at least ten student records.
2. Create a Select query to display all fields.
3. Create a query that displays StudentName and Course only.
4. Find all students enrolled in ADCA.
5. Find students whose fees are greater than 5000.
6. Find students whose fees are between 4000 and 10000.
7. Display students in alphabetical order.
8. Display students with the highest fees first.
9. Display each course only once using DISTINCT.
10. Save each query with a meaningful name.

Goal: Practice retrieving selected fields, filtering data, sorting results, and removing duplicate query output.

1. What is a Select query?
2. How do you select all fields from a table?
3. What is the purpose of the WHERE clause?
4. What is the difference between AND and OR?
5. What is the purpose of ORDER BY?
6. What do ASC and DESC mean?
7. What does DISTINCT do?
8. What is an INNER JOIN?