GoalCombine criteria with OR and NOT, use the wildcard *, and write a query that takes fields from two linked tables.
New words
OR · a record is found if it meets the conditions in at least one rowNOT · a record is found only if it does not match the valuewildcard · a symbol, such as *, that stands for any characters in a text searchrelationship · the link between two tables that lets one query use fields from both
Explanation
Criteria in the same row are joined by AND. Criteria in different rows are joined by OR: the second condition goes in the row marked or. A record is found if it satisfies every condition in at least one row. NOT turns a text condition round, so NOT "Road" finds every type except Road. The wildcard * stands for any characters, including none. T* finds text beginning with T, and *e finds text ending in e. A query can also take fields from two tables when a relationship links them. The grid then has a Table row that tells where each field lives, and the linked key makes the right records meet.
Wanted
Where the conditions go
Result size
A and B
same Criteria row
usually fewer records
A or B
A in Criteria, B in the or row
usually more records
not A
NOT before the value
starts with T
"T*" in the field
Worked examples
Field
Model
Type
Gears
Table
Bikes
Bikes
Bikes
Sort
Show
yes
yes
yes
Criteria
"Folding"
or
>=27
OR or AND? The first grid has Type = "Folding" in the Criteria row and Gears >= 27 in the or row. A bike is found if it is a Folding bike or has at least 27 gears: Falcon (27 gears), Nimbus (6) and Tulip (3), 3 records. The second grid puts both conditions in the same row. No Folding bike has 27 or more gears, so it finds 0 records.
Field
Model
Type
Available
Table
Bikes
Bikes
Bikes
Sort
Show
yes
yes
no
Criteria
NOT "Road"
"Yes"
or
NOT and the wildcard. The first query lists the available bikes that are not Road bikes: Trailer, Tiger, Nimbus, Ridge, Tulip and Titan, 6 records. A second query, with Model "T*" and Type "Road" in the same row, first finds the models that begin with T: Tornado, Trailer, Tiger, Tempo, Tulip and Titan. Only Tempo is a Road bike, so 1 record is found. The pattern "*e" would find Breeze and Ridge.
Field
Model
Town
Available
Table
Bikes
Branches
Bikes
Sort
Show
yes
yes
no
Criteria
NOT "Lakeside"
"Yes"
or
Two tables. Bikes holds the Branch_ID of the branch where each bike is kept, and Branches holds the Town for each Branch_ID. The Branches table holds B1 Rivermouth, B2 Hillton, B3 Lakeside and B4 Eastport. The grid takes Model from Bikes and Town from Branches, and the relationship through Branch_ID joins them. Available bikes not kept in Lakeside are 7: Breeze (Rivermouth), Trailer (Rivermouth), Tiger (Eastport), Nimbus (Hillton), Tempo (Hillton), Ridge (Eastport) and Tulip (Rivermouth).
Class activity
Human query (no computer). Write eight pupils’ first names with a favourite sport and a yes/no for ‘plays in a team’ on cards. Place the cards on the floor. A caller reads one query, such as ‘sport is football OR name starts with A’, and the team of players picks up the matching cards. Count the cards. Then change OR into AND, run it again and compare the counts. Say which query finds more cards and why.
Practice
1
The Orchard table is shown. A query has Fruit = "Pear" in the Criteria row and Height_m > 4.5 in the or row. Which Tree_IDs are found?
Tree_ID
Fruit
Height_m
Year_planted
Healthy
1
Apple
3.5
2019
Yes
2
Pear
2.8
2021
Yes
3
Plum
4.1
2018
No
4
Apple
4.6
2017
Yes
5
Cherry
3.2
2020
Yes
6
Pear
5.0
2016
No
Pear: trees 2 and 6. Height above 4.5: tree 4 (4.6) and tree 6 (5.0). A tree matches if it meets either row, so the result is trees 2, 4 and 6.
2
The Instruments table is shown. Which instruments are found by Name "G*", and which by Name "*n"?
Name
Section
Guitar
strings
Gong
percussion
Flute
wind
Drum
percussion
Horn
brass
Violin
strings
"G*" finds Guitar and Gong. "*n" finds Horn and Violin, the names that end with n.
3
Write the criteria (with the field each belongs to) for: Bikes that are not Road bikes, cost less than 15 and have a Model beginning with T. Which of the bikes in the table are found?
Bike_ID
Model
Type
Gears
Price
Available
Branch_ID
1
Breeze
Road
21
12.00
Yes
B1
2
Tornado
Mountain
18
15.50
No
B2
3
Trailer
Mountain
24
16.00
Yes
B1
4
Comet
Road
14
9.50
Yes
B3
5
Tiger
Mountain
21
14.00
Yes
B4
6
Falcon
Road
27
18.00
No
B3
7
Nimbus
Folding
6
8.50
Yes
B2
8
Tempo
Road
18
11.00
Yes
B2
9
Ridge
Mountain
24
17.50
Yes
B4
10
Tulip
Folding
3
7.00
Yes
B1
11
Zephyr
Road
21
13.00
No
B4
12
Titan
Mountain
21
15.00
Yes
B3
Type: NOT "Road"; Price: <15; Model: "T*", all in the same row. Tornado costs 15.50, Trailer 16.00, Tiger 14.00, Tulip 7.00 and Titan 15.00. Only Tiger and Tulip cost less than 15, so the bikes found are Tiger and Tulip.
4
A Clubs table holds C1 Chess, C2 Art and C3 Choir. The Pupils table is shown and linked by Club_ID. A query shows Name and Club_name with the criterion Club_name NOT "Art". List the results.
Pupil_ID
Name
Club_ID
P1
Ali
C1
P2
Mia
C2
P3
Leo
C1
P4
Ana
C3
P5
Omar
C2
Ali and Leo belong to C1 (Chess), and Ana belongs to C3 (Choir). Mia and Omar are in C2 (Art), so they are left out. Results: Ali Chess, Leo Chess, Ana Choir.