Criteria and hidden fields: sharpening the question
ICT Prep 1 Term 1, Lesson 9: pp. 87–97
GoalSharpen a question with criteria and hidden fields, and say why hidden is not deleted.
New words
criterion · one condition, such as “Class is 1A” or “English is more than 13”hidden field · a field that the query uses but does not show in the resultmore than · a comparison: 15 is more than 13, but 12 is notboth conditions · a record must meet every criterion in the grid to appear
Explanation
We can sharpen a question by adding criteria. If we put a criterion in two fields, a record must meet both of them to appear. Take the question “Which pupils are in class 1A and have an English mark of more than 13?” The criterion for Class is 1A, and the criterion for English is more than 13. Mina is in 1A with 15, so she appears. Omar is in 1A but has 12, so he does not. Salma has 17 but is in 1B, so she does not either. The result is only Mina. We can also hide fields. If we use the English field only for the criterion, we remove the tick from its Show row, and the answer shows just the names. Hidden does not mean deleted: the field is still in the table, and the query just does not display it. Try it next with another invented question.
Field
Name
Class
English
Table
Pupils
Pupils
Pupils
Show
yes
yes
no
Criteria
1A
>13
Worked examples
A
B
C
D
1
id
Name
Class
English
2
1
Mina
1A
15
3
2
Salma
1B
17
4
3
Omar
1A
12
5
4
Nour
1C
19
6
5
Youssef
1B
14
Which pupils have an English mark of more than 14? Check each mark: 15 yes, 17 yes, 12 no, 19 yes, 14 no (14 is not more than 14). The result is Mina, Salma and Nour: three records.
A
1
Name
2
Salma
Which pupils are in class 1B with English of more than 15? Salma has 1B and 17, so she appears. Youssef has 1B but 14, so he does not. The result: Salma only.
hide the field in the answer→the table still has it
After we hide the English field in the answer, the result shows only names. We open the table and the English marks are still there, so hidden is not deleted.
Class activity
Make six invented pupil cards with a class and a mark. Write two queries, each with two criteria, and find the result by sorting the cards. Then say which field you would hide in the answer, and prove to a partner that the hidden data is still on the card.
Practice
1
English marks: 15, 17, 12, 19, 14. Which are more than 14?
15, 17 and 19.
2
When there are criteria in two fields, what must a record do to appear?
Meet both criteria.
3
How do we hide a field in the answer, and is its data deleted?
We remove its tick in the Show row; no, the data stays in the table.
4
Which pupils are in class 1A with English of more than 13?
