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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

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 DESC

Result (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    | Long

In 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     | Brown

Make 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).

  1. ```sql SELECT ArtName, ActualPrice - MinPrice AS PriceDifference FROM tblArt WHERE Status = 'Sold' AND ActualPrice - MinPrice > 150 ORDER BY PriceDifference DESC ```
  2. Access grid: PriceDifference: [ActualPrice]-[MinPrice] with criterion >150 underneath, and Status criterion "Sold".
  3. 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.

Key idea: Calculated fields compute from other fields at run time; always name them (HireCost: [DailyRate]*[Days]) and format them. CASE WHEN in SQL, IIf in Access, for conditional values. Parameter queries prompt for a criterion such as [Enter the town].

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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).

  8. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.