All subjects · Information Technology · WIT14 3 hour practical exam on database solutions

Reports and charts

Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions

Reports turn query results into formatted, printable output (specification 19.1.3 and 19.1.5). Exam papers have asked for grouped reports with totals and maximums matching a given layout, and charts in reports. Most marks are for details that the default wizard does not add.

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

Practise this lesson

Building a report from a query

1. Write and test a query that selects exactly the right records and fields (the report is only as good as its query).

2. Create the report from the query, using the wizard to start if you wish.

3. Add grouping (for example group by bike type), so each group has a header and a footer.

4. Add summary calculations in group or report footers: =Sum([Income]), =Max([Income]), =Count(*).

5. Format: a meaningful title in the report header, currency formatting, aligned columns, readable labels, no truncated text, sensible use of space, shading if the layout asks for it.

6. Check Print Preview: page breaks, widths and totals.

The query behind a grouped income report:

SELECT b.BikeType, b.Model, COUNT(*) AS TimesHired, SUM(b.DailyRate * h.Days) AS Income
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
GROUP BY b.BikeType, b.Model
ORDER BY b.BikeType, b.Model

Result (6 rows):

BikeType | Model      | TimesHired | Income
---------+------------+------------+-------
Electric | Volt E2    | 3          | 175.00
Hybrid   | City Glide | 2          | 37.50
Hybrid   | Commuter   | 1          | 20.00
Mountain | Ridge Pro  | 1          | 44.00
Mountain | Trail 500  | 2          | 108.00
Road     | Speedster  | 2          | 120.00

Report sections

SectionTypical content
Report headerTitle, logo, date printed (appears once at the start)
Page headerColumn headings (on every page)
Group headerThe group value, for example 'Bike type: Mountain'
DetailOne line per record
Group footerGroup totals: =Sum([Income]), =Max([Income])
Report footerGrand totals (once, at the end)
Page footerPage numbers: ="Page " & [Page] & " of " & [Pages]

A total in a group footer sums that group; the same total in the report footer sums everything.

Charts

Choose a chart that suits the data:

Every chart needs a title, axis titles, sensible scales (whole numbers for counts, not 0.5 steps) and data labels or a legend where useful. The 2024 and 2025 examiner reports both mention missing axis titles, and 2025 mentions unsuitable half-unit scales.

The data for an income-by-type chart comes from a grouped query:

SELECT b.BikeType, SUM(b.DailyRate * h.Days) AS Income
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
GROUP BY b.BikeType
ORDER BY Income DESC

Result (4 rows):

BikeType | Income
---------+-------
Electric | 175.00
Mountain | 152.00
Road     | 120.00
Hybrid   | 57.50

Output formats

Specification 19.1.5 asks you to produce output in suitable formats: on screen, printed reports, charts, or exported files (PDF for a fixed, printable copy; spreadsheet or CSV for further analysis; HTML for the web). Choose the format that suits the recipient and what they will do with it.

Worked example

A report must show each customer's hires grouped by customer, with each customer's total days and the highest single hire length, plus a grand total of days. Plan it.

  1. Query: customer name, HireDate, Days from tblCustomer joined to tblHire.
  2. Group on CustomerID (showing the name in the group header); detail lines show HireDate and Days.
  3. Group footer: =Sum([Days]) labelled 'Total days' and =Max([Days]) labelled 'Longest hire'.
  4. Report footer: =Sum([Days]) labelled 'Total days for all customers' = 17.
  5. Report header: title 'Hire summary by customer'; check in Print Preview.

Answer: Group by customer, Sum and Max in the group footer, grand total in the report footer.

Key idea: Report = tested query + grouping + summary calculations (=Sum, =Max, =Count in footers) + formatting + title, checked in Print Preview. Group footer totals a group, report footer totals all. Charts need a title, axis titles and sensible scales; choose column, line or pie to suit the data.

Check yourself

  1. How many rows does this query return on the Harbour Bike Hire database? ```sql SELECT b.BikeType, b.Model, COUNT(*) AS TimesHired, SUM(b.DailyRate * h.Days) AS Income FROM tblHire h JOIN tblHireItem i ON i.HireID = h.HireID JOIN tblBike b ON b.BikeID = i.BikeID GROUP BY b.BikeType, b.Model ORDER BY b.BikeType, b.Model ```
    Show answer

    6. Each bike hired at least once forms one detail line: 6.

  2. Using the report query, what would =Max([Income]) in the report footer show? ```sql SELECT MAX(Income) FROM (SELECT b.BikeType, b.Model, COUNT(*) AS TimesHired, SUM(b.DailyRate * h.Days) AS Income FROM tblHire h JOIN tblHireItem i ON i.HireID = h.HireID JOIN tblBike b ON b.BikeID = i.BikeID GROUP BY b.BikeType, b.Model ORDER BY b.BikeType, b.Model) ```
    Show answer

    175.00. The highest income for a single model is 175.00.

  3. Using the report query, what would =Sum([Income]) show in the Mountain group footer? ```sql SELECT SUM(Income) FROM (SELECT b.BikeType, b.Model, COUNT(*) AS TimesHired, SUM(b.DailyRate * h.Days) AS Income FROM tblHire h JOIN tblHireItem i ON i.HireID = h.HireID JOIN tblBike b ON b.BikeID = i.BikeID GROUP BY b.BikeType, b.Model ORDER BY b.BikeType, b.Model) WHERE BikeType = 'Mountain' ```
    Show answer

    152.00. Mountain group: Trail 500 and Ridge Pro income added = 152.00.

  4. Where should a grand total of income for all bikes go?
    • The detail section
    • The report footer
    • The page header
    • The group header
    Show answer

    The report footer. The report footer appears once at the end, after all groups.

  5. Which chart best shows how the number of hires changes month by month?
    • A pie chart
    • A scatter chart of customer IDs
    • A table with no chart
    • A line chart
    Show answer

    A line chart. Line charts show change over time.

  6. According to Pearson's examiner reports, which chart weakness commonly lost marks?
    • Using a title
    • Missing axis titles
    • Using a legend
    • Showing data labels
    Show answer

    Missing axis titles. Both the 2024 and 2025 reports mention missing axis labels or titles.

  7. A manager wants a monthly report emailed to the board, who must not be able to change it easily. Which format suits best?
    • CSV
    • The .accdb database file
    • A plain text file
    • PDF
    Show answer

    PDF. PDF preserves layout and is not designed for editing.

  8. Explain why a report produced only with the default wizard settings is likely to score few marks in the exam.
    Show answer

    The wizard does not add the required title, grouping, calculations and formatting, so it will not match the layout and features the scenario asks for.. Marks are for specific features (title, grouping, calculations, formatting, layout) that must be added and checked against the brief.

Practise with instant feedback

Sources

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