Detailed · Deep study
1Calculations
A calculated field is created in a query from other fields, e.g. a field TotalCost = [Price] * [Quantity]. A calculated control is a text box on a form or report that shows a calculation at run time, e.g. =Sum([TotalCost]) in a report footer.
TotalCost: [Price]*[Quantity]
=Sum([TotalCost])
=Avg([Mark])
=Max([Price])
=Count([MemberID])2Sorting
Sort records into ascending (A–Z, 0–9, oldest first) or descending order on a single field, or on multiple criteria: e.g. sort by Surname ascending, then by FirstName ascending for people with the same surname.
3Searching with queries
A query selects a subset of records that meet criteria; it can use one criterion or several in different fields (on the same row = AND; on different rows = OR).
| Operator / wildcard | Example criterion | Finds |
|---|---|---|
| = | ="Nairobi" | records where the field equals Nairobi |
| >, <, >=, <= | >=50 | values of 50 or more |
| <> | <>"Paid" | all records except Paid |
| AND | >=10 AND <=20 | values from 10 to 20 inclusive |
| OR | "Red" OR "Blue" | either value |
| NOT | NOT "Science" | everything except Science |
| LIKE with * (any characters) | LIKE "M*" | values starting with M |
| ? (one character) | LIKE "B?ll" | Ball, Bell, Bill, Bull |
4Exam focus
Key words: calculated field; calculated control; sort; ascending; descending; query; criteria; AND; OR; NOT; LIKE; wildcard.
Quick self-check
Answer in your head first, then reveal.
LIKE "S*" (2).
Calculated field is created in a query (1); calculated control is on a form/report and calculated at run time (1).
Sort first on the main field, e.g. surname (1), then on a second field for records with the same value, e.g. first name (1).
