Multiple Conditions in MS Access

Multiple conditions are two or more requirements used together in a query. They help you find records that match particular combinations of values.

Example: Students Table

Suppose you want to find ADCA students whose fees are greater than 5000. You need two conditions:

• Course must be ADCA.
• Fees must be greater than 5000.

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

This query returns Amit and Rahul because both records satisfy the two conditions.

The AND operator returns records only when all specified conditions are true.

Example 1: Course and Fees

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

This query displays ADCA students whose fees are greater than 5000.

Example 2: Fees Between 5000 and 10000

SELECT *
FROM Students
WHERE Fees >= 5000
AND Fees <= 10000;

This query displays students whose fees are at least 5000 and at most 10000.

Example 3: Course and Student Name

SELECT *
FROM Students
WHERE Course = "ADCA"
AND StudentName LIKE "A*";

In the default Access ANSI-89 wildcard syntax, this query finds students enrolled in ADCA whose names begin with A.

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

Example 1: Two Courses

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

This query displays students enrolled in ADCA or Tally Prime.

Example 2: Low or High Fees

SELECT *
FROM Students
WHERE Fees < 5000
OR Fees > 10000;

This query returns students whose fees are below 5000 or above 10000.

Example 3: Two Student Names

SELECT *
FROM Students
WHERE StudentName = "Amit"
OR StudentName = "Priya";

This query returns records for Amit or Priya.

You can combine AND and OR in the same query to create more specific conditions.

Example: ADCA Students or Students with High Fees

SELECT *
FROM Students
WHERE Course = "ADCA"
OR Fees > 10000;

This query returns students who are enrolled in ADCA or whose fees are greater than 10000.

Example: ADCA Students with High Fees, or Python Students

SELECT *
FROM Students
WHERE (Course = "ADCA" AND Fees > 5000)
OR Course = "Python";

This query returns:

• ADCA students whose fees are greater than 5000.
• All Python students, regardless of their fees.

Parentheses make it clear which conditions should be evaluated together.

You can create multiple conditions without writing SQL manually by using the query design grid.

1. Open MS Access and select the Create tab.
2. Click Query Design.
3. Add the Students table.
4. Add the Course and Fees fields.
5. Enter "ADCA" in the Course field's Criteria row.
6. Enter >5000 in the Fees field's Criteria row.
7. Keep both criteria on the same Criteria row.
8. Click Run (!) to see the results.

Example Design Grid

Because both conditions are in the same Criteria row, Access combines them using AND.

Using OR in the Design Grid

To find students enrolled in ADCA or Tally Prime, you can place the criteria on separate rows:

This arrangement returns records that match either course.

The NOT operator reverses a condition. It is useful when you want to exclude certain values.

Example 1: Exclude ADCA Students

SELECT *
FROM Students
WHERE NOT Course = "ADCA";

This query returns records where Course is not ADCA. Records with a Null Course value will not satisfy this condition either.

Example 2: Fees Not Between 5000 and 10000

SELECT *
FROM Students
WHERE NOT (Fees BETWEEN 5000 AND 10000);

This query finds students whose fees fall outside the specified range, excluding records where Fees is Null.

Multiple conditions can also be used with Date/Time fields.

Example: Admissions During 2026

SELECT StudentName, AdmissionDate
FROM Students
WHERE AdmissionDate >= #1/1/2026#
AND AdmissionDate < #1/1/2027#;

This query finds admission dates from the start of January 1, 2026, up to but not including January 1, 2027. Using an exclusive upper boundary also works for date fields that contain times.

Example: ADCA Students Admitted During 2026

SELECT *
FROM Students
WHERE Course = "ADCA"
AND AdmissionDate >= #1/1/2026#
AND AdmissionDate < #1/1/2027#;

This query requires all three conditions to be satisfied: the course must be ADCA and the admission date must fall within 2026.

Create a table named Students with these fields:

• StudentID — AutoNumber
• StudentName — Short Text
• Course — Short Text
• Fees — Currency
• AdmissionDate — Date/Time

Tasks

1. Enter at least ten student records.
2. Find ADCA students whose fees are greater than 5000.
3. Find students enrolled in ADCA or Tally Prime.
4. Find students whose fees are below 5000 or above 10000.
5. Find ADCA students with fees above 5000, or any Python students.
6. Exclude students enrolled in ADCA.
7. Find students admitted during 2026.
8. Find ADCA students admitted during 2026.
9. Create at least three queries using Design View.
10. Save each query with a meaningful name.

Goal: Practice combining AND, OR, NOT, numeric criteria, text criteria, and date conditions.

1. What are multiple conditions in MS Access?
2. What is the difference between AND and OR?
3. How can you combine AND and OR in one query?
4. Why are parentheses useful in complex conditions?
5. How can you exclude ADCA students?
6. How do you place AND criteria in Query Design View?
7. How do you specify OR criteria in Query Design View?
8. How can you filter admission dates for the year 2026?