Applying multiple criteria means telling the database to keep only the records that meet several conditions at once or in alternatives. It appears whenever a practical task says “show members who are in this club and have that attendance”.
This lesson belongs to database queries and reports. The comparison symbols used here are =, <> (not equal), >, >=, < and <=. Your software may show them in a design grid or as typed text, but the logic is the same.
How do AND, OR and brackets work?
AND requires every condition to be true. OR requires at least one. When both appear in the same query, AND is worked out first, so brackets are needed to force a different order.
- Rewrite the task as a sentence with each condition on its own.
- Decide how the conditions join: all together (AND) or any one (OR).
- Group alternatives in brackets when an OR sits inside an AND.
- Work out the expected records by hand from the source table before you run anything.
- Run it and compare the count and the names.
Worked example
The table is the invented Harbour Hill Clubs table from the module page: ten records with ID, Name, Year, Club, Fee and Sessions (out of 12).
Task: list members of Drama or Robotics who attended at least 9 sessions.
Step 1, sentence: (Club is Drama or Club is Robotics) and Sessions is at least 9.
Step 2, criteria: (Club = “Drama” OR Club = “Robotics”) AND Sessions >= 9
Step 3, by hand: Drama members are Bella (12), Chen (9), Gopal (6). Robotics members are Evan (7), Farah (12), Jia (5). Keep those with 9 or more: Bella, Chen, Farah.
Step 4, result: three records, Bella, Chen and Farah. If the query shows these three, it matches the hand check.
The mistake to watch for
Leaving out the brackets:
Club = “Drama” OR Club = “Robotics” AND Sessions >= 9
Because AND is worked out first, this means: Drama members of any attendance, plus Robotics members with 9 or more.
That returns Bella, Chen, Gopal and Farah, four records. Gopal attended only 6 sessions, so he should not appear. The query ran without any error message, so only the hand check reveals the problem.
The correction is to bracket the two OR conditions together, as in the worked example, and to compare the count with your expectation.
Check yourself
Use the invented table and work out each answer by hand first.
1. Which members are in Year 11 AND pay a fee above RM40?
Show answer
Year 11 members are Bella (45), Evan (60) and Hui (30). Fee above 40 keeps Bella and Evan. Bella and Evan, two records.
2. Write the criteria for “attended between 8 and 11 sessions inclusive” and list the matches.
Show answer
Sessions >= 8 AND Sessions <= 11. Matches: Aiman (8), Chen (9), Dewi (11), Hui (10). Four records. Bella, Farah and Imran have 12, so they are out.
3. How many members are NOT in Chess?
Show answer
Club <> “Chess”. Chess has four members, so the other six are Bella, Chen, Evan, Farah, Gopal and Jia.
Where this leads next
Once the right records appear, sort records with a secondary key to put them in the requested order. You can also test queries in the read-only SQL practice lab and confirm your evidence with the ICT practical task and evidence checker, then try the practice set.
If bracket logic still feels uncertain, our teachers can practise it with you in online one-to-one ICT tuition.