All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Calculated fields, conditional logic and parameter queries
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Specification 19.1.4 asks you to use calculated fields. Exam papers ask for differences between prices, totals with fees, and values that depend on a condition, and teaching materials also use parameter queries that ask the user for a value.
Calculated fields
A calculated field works out a value from other fields every time the query runs, so it is never out of date. Give it a meaningful name (alias):
SELECT h.HireID, b.Model, h.Days, b.DailyRate,
b.DailyRate * h.Days AS HireCost,
CASE WHEN h.Days >= 3 THEN 'Long' ELSE 'Short' END AS HireLength
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
WHERE b.DailyRate * h.Days > 50
ORDER BY HireCost DESCResult (4 rows):
HireID | Model | Days | DailyRate | HireCost | HireLength
-------+-----------+------+-----------+----------+-----------
108 | Volt E2 | 3 | 35.00 | 105.00 | Long
106 | Trail 500 | 4 | 18.00 | 72.00 | Long
103 | Speedster | 3 | 20.00 | 60.00 | Long
108 | Speedster | 3 | 20.00 | 60.00 | LongIn an Access design grid you type the name, a colon and the expression in a blank Field cell: HireCost: [DailyRate]*[Days]. Criteria can go under the calculated field too. Set its Format property (for example Currency) so it displays properly.
Conditional logic
Calculations can depend on a condition. SQL uses CASE WHEN ... THEN ... ELSE ... END (above). Access uses the IIf function: HireLength: IIf([Days]>=3,"Long","Short"). Examples: a discount only for hires over 3 days, a 'Reorder' flag when stock falls below a level, a fee of 5% shown as [ActualPrice]*0.05.
Parameter queries
A parameter query asks the user for a criterion each time it runs, so one query serves many searches. In Access you type a prompt in square brackets in the Criteria row, for example [Enter the town], and the user types Millbrook. In SQL the value is supplied as a parameter (a placeholder ? or a named value from a form). The effect is the same as WHERE Town = 'Millbrook':
-- the table p holds the value the user typed: 'Millbrook'
SELECT FirstName, Surname FROM tblCustomer WHERE Town = (SELECT Town FROM p)Result (2 rows):
FirstName | Surname
----------+--------
Ben | Okafor
Ellis | BrownMake prompts clear ('Enter the town, for example Portview'), and combine with wildcards (Like [Enter start of surname] & "*" in Access) for partial matches.
Worked example
Show each sold work's name and the difference between its actual and minimum selling prices, only where the difference is over 150 (a task like June 2024 Question 3a). Use a small table tblArt(ArtName, MinPrice, ActualPrice, Status).
- ```sql SELECT ArtName, ActualPrice - MinPrice AS PriceDifference FROM tblArt WHERE Status = 'Sold' AND ActualPrice - MinPrice > 150 ORDER BY PriceDifference DESC ```
- Access grid: PriceDifference: [ActualPrice]-[MinPrice] with criterion >150 underneath, and Status criterion "Sold".
- Note > not >=: the mark scheme for that paper accepted >150 or >=151, and the examiner report notes candidates using the wrong comparison operator.
Answer: A named calculated field with criteria on the calculation.
Check yourself
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT h.HireID, b.Model, h.Days, b.DailyRate,
b.DailyRate * h.Days AS HireCost,
CASE WHEN h.Days >= 3 THEN 'Long' ELSE 'Short' END AS HireLength
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
WHERE b.DailyRate * h.Days > 50
ORDER BY HireCost DESC
```
Show answer
4. Bike-hire lines costing over 50: 108, Volt E2, 3, 35.00, 105.00, Long; 106, Trail 500, 4, 18.00, 72.00, Long; 103, Speedster, 3, 20.00, 60.00, Long; 108, Speedster, 3, 20.00, 60.00, Long. That is 4 rows.
- What is the highest value of DailyRate x Days for any bike on any hire?
```sql
SELECT MAX(b.DailyRate * h.Days) FROM tblHire h JOIN tblHireItem i ON i.HireID = h.HireID JOIN tblBike b ON b.BikeID = i.BikeID
```
Show answer
105.00. The largest single bike-hire cost is 105.00.
- How many hires are labelled 'Long' by CASE WHEN Days >= 3 THEN 'Long' ELSE 'Short' END?
```sql
SELECT COUNT(*) FROM tblHire WHERE CASE WHEN Days >= 3 THEN 'Long' ELSE 'Short' END = 'Long'
```
Show answer
3. Hires of 3 or more days: 3.
- A parameter query asks for the town and the user types Millbrook (held here in table p). How many rows are returned?
```sql
SELECT FirstName, Surname FROM tblCustomer WHERE Town = (SELECT Town FROM p)
```
Show answer
2. Millbrook customers: Ben, Okafor; Ellis, Brown. That is 2 rows.
- A sale's actual selling price is 1240. The salesperson's fee is 5% of it. What is the fee?
Show answer
62.00. 1240 x 0.05 = 62.00.
- Why should you rename a calculated field from Expr1 to, for example, PriceDifference?
- Because Expr1 fields cannot be sorted
- So the field is stored in the table
- So the column heading is meaningful to users and in reports, as examiners expect
- Because Access rejects Expr1
Show answer
So the column heading is meaningful to users and in reports, as examiners expect. The 2025 examiner report advises that whenever a calculation is used in a query, candidates should always rename the field appropriately.
- Which Access expression shows 'Reorder' when Stock is below 5 and 'OK' otherwise?
- IIf([Stock]<5,"Reorder","OK")
- If Stock<5 Reorder
- IIf([Stock]>5,"Reorder","OK")
- Sum([Stock]<5)
Show answer
IIf([Stock]<5,"Reorder","OK"). IIf(condition, value if true, value if false).
- Why calculate HireCost in a query rather than storing it in tblHire?
- Queries cannot show stored fields
- It is always up to date and cannot contradict the rate and days stored
- Stored fields cannot be Currency
- It makes the table load faster
Show answer
It is always up to date and cannot contradict the rate and days stored. Derived values that are stored can become inconsistent when the source fields change.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 question paper, June 2024
- Pearson: WIT14/01 examiner report, June 2024
- Pearson: WIT14/01 examiner report, June 2025
- Pearson: IAL Information Technology scheme of work for Unit 4
Last updated 8 November 2026. Spotted a mistake? Tell us.