Calculated field/control — worked out at run time
A calculated field (query) or calculated control (form/report) stores no value: it holds an expression on other fields that the engine works out at run time whenever the object opens. Arithmetic (+ - * /) combines fields, e.g. TotalCost: [Quantity]*[UnitPrice]-[Discount]. Aggregate functions summarise a set of records: SUM (total), AVERAGE (mean), MAXIMUM (largest), MINIMUM (smallest), COUNT (number of records — not a total).
Sorting — one field or several, each own direction
Sorting orders records on one or more fields. A single-criterion sort uses one field, e.g. Surname ascending (A→Z). A multi-level sort uses a primary field, then secondary field(s) to break ties — e.g. Class ascending, then Mark descending within each class. The primary is evaluated first; a secondary only orders records tied on it. Ascending puts smallest/earliest/A first, descending the reverse; each field takes its own direction.
Query — operators, AND/OR/NOT and wildcards
A query selects a subset of records by testing fields against criteria. Operators: = equal, <> not equal, > / < greater/less, >= / <= or-equal, LIKE matches a text pattern. AND keeps records where every condition is true (narrows); OR where at least one is (widens); NOT reverses a condition. Wildcards fill unknown characters — commonly * for any run, ? for one, e.g. LIKE "*mit*" contains "mit".
Drawn from real examiner reports.
SUM/AVERAGE where MAX, MIN or COUNT is needed
Candidates default to SUM or AVERAGE, but "longest"/"highest"/"most" → MAXIMUM; "shortest"/"earliest"/"lowest" → MINIMUM; "how many records" → COUNT. SUM and AVERAGE total or average a numeric field; COUNT adds nothing — it counts records that satisfy the criteria, so it works on any field type. Naming SUM to "count the records" scores no credit.
w23 P02 Q22, s23 P21 — the correct aggregate had to be chosen (MAX longest, MIN earliest, COUNT number of records); SUM and AVERAGE were repeatedly misused in their place.
">1975" is not "1975 and later"
>1975 means strictly after 1975 — it excludes 1975 itself. "1975 and later" (inclusive) must be >=1975. Mixing > with >=, or < with <=, silently returns the wrong records, and candidates also confuse the direction of > and <. Read the wording: "onwards"/"at least"/"or more" includes the boundary (>=); "over"/"more than"/"after" excludes it (>).
w23 P02 Q21/Q22, s23 P21 — ">1975" does not mean "1975 and later" (needed >=); candidates also confused > with <.
Excluding several values needs AND, not OR
To select records that are neither "Red" nor "Blue", use <>"Red" AND <>"Blue" — AND keeps only records satisfying both. <>"Red" OR <>"Blue" is the trap: almost every record satisfies at least one side (a Red record still is not Blue), so OR selects nearly everything, including the excluded values. Here, exclusion needs AND, not OR.
w23 P02 Q21/Q22, s23 P21 — excluding more than one value needed AND, not OR.
Summary total in the wrong report section
A summary calculation that should appear once (a grand total, overall average, or record count) belongs in the report footer. Identification details (candidate/centre number, page numbers) that appear on every printed page belong in the page footer. Putting a summary total in the page footer makes it print on every page, not once at the end.
w23 P02 Q22, s23 P21 — summary calcs expected in the report footer (once); identification details in the page footer (every page).
Calculated time left unformatted
A calculation returning a time must be shown in the format the task specifies, e.g. hh:mm:ss, not as a raw decimal number of hours. An unformatted or wrongly-formatted result does not match the evidence the mark scheme expects — even when the underlying calculation is correct. Apply the required display format before printing the evidence.
w23 P02 Q22, s23 P21 — a calculated time result had to be formatted hh:mm:ss.
Sort left in the query, not the report
When a task wants records ordered in a report, set the sort in the report's own structure (its Group & Sort area) — not in the query, the report wizard, or a datasheet re-order. Setting it in the wrong place makes the sort field jump to the front and causes multi-field sorts to fail. A datasheet click-to-sort is not part of the saved design and will not persist.
w23 P02 Q21/Q22 — sorting had to be set in the report structure, else the sort field jumped to the front and multi-field sorts failed.
Build a criterion field-by-field
Identify three things exactly: the field name, the operator and the value/pattern, matched to the data's format (dates, currency, text case). With more than one condition, choose AND vs OR — must all be true, or is one enough? Excluding several values needs AND.
Match the aggregate to the command word
Underline the command word: "total" → SUM; "average" → AVERAGE; "longest"/"highest" → MAXIMUM; "shortest"/"earliest" → MINIMUM; "how many" → COUNT. Then decide: a calculated field (arithmetic per record) or a summary function (one value across many records)?
Fix the sort in the structure the task names
Identify the primary field and direction first, then any secondary field(s) and their direction. Set the sort where the task specifies — a report's Group & Sort area, or the query's sort row — not a datasheet column-heading click, which is not saved.
Show the expression or design grid as evidence
In a printed or screenshotted answer, show the calculated field's expression (formula view) or the query's design grid with criteria visible. A correct number with no visible expression or criteria is not enough evidence — the mark scheme credits the working.
Full notes, flashcards, Q&A and the topic quiz for every premium subject.
Premium plans are US$8.99/month or US$49.99/year — first month free.
Studying with a parent's blessing? Show them this.
