A query finds the records in a table that meet some criteria, and shows the fields you choose. In Microsoft Access you build one in a grid, called query by example: one column per field, with rows for how to sort it, whether to show it and its criteria. This grid works the same way, and shows the SQL it turns into, live, and the result.
- Field: choose a field from Books (or click it in the field list). Sort: Ascending (A to Z, smallest first) or Descending; when several fields are sorted, the leftmost is sorted first. Show: untick it to use a field's criteria without showing the field.
- Criteria on the same row must all be true (AND). Criteria on different rows are alternatives (OR): a record is found if it meets every criterion on any one row.
- Text goes in double quotes:
"Fantasy" (type Fantasy and Access adds them). Numbers don't: >2020, <=300, <>45. A Yes/No field takes Yes or No.
Between 2010 And 2015 includes both ends. Not "Crime" is everything except Crime.- Wildcards go with
Like: * stands for any number of characters, ? for exactly one. Like "The*" begins with The; Like "*fire*" contains fire; Like "??? *" is three characters, then a space.
- A calculated field is a new name, a colon, then an expression with fields in square brackets:
Sale: [Price]*0.9.
In SQL, the shown fields follow SELECT, the criteria become WHERE (AND within a row, OR between rows), and the sorts ORDER BY. SQL uses % and _ as its wildcards where Access uses * and ?.
Common exam mistakes: putting two criteria that must both be true on different rows (that's OR); putting "Mystery" and "Science" on the same row in one field (no record is both); forgetting the quotes, or the wildcard, in a Like criterion; using > where the question says "or more" (>=); leaving a criteria field showing when the question lists only some fields; sorting by the wrong field first.
Objective: Cambridge IGCSE ICT (0417) section 18, databases: performing searches with single and multiple criteria, using AND, OR, NOT, LIKE, wildcards and comparison operators, sorting, and calculated fields in queries; Cambridge International AS Level IT (9626) database and file concepts: queries and SQL.
Where this fits
- Cambridge: Cambridge IGCSE Information and Communication Technology (0417); Cambridge A Level Information Technology (9626); Cambridge AS Level Information Technology (9626) Goes beyond Cambridge IGCSE Information and Communication Technology (0417): The live SQL view goes beyond 0417, which builds queries in the database's grid only.
- NCEA Level 2 Digital Technologies: 91892 Use advanced techniques to develop a database
- Pearson Edexcel International: Edexcel International GCSE ICT (4IT1); Edexcel International A Level Information Technology Goes beyond Edexcel International GCSE ICT (4IT1): Wildcards and calculated fields go beyond 4IT1's searches.
