Be able to produce reports to display data including displaying all the required data and labels in full

Topic 18 – Databases (Cambridge IGCSE 0417)

Learning Objective

Produce a complete, correctly formatted report that displays all required data fields and full column labels, using appropriate layout, calculations and e‑safety considerations, and that meets every requirement of the Cambridge IGCSE 0417 syllabus.

1. Database Foundations

1.1 Types of Databases

1.2 Key Database Concepts (expanded)

• Table – collection of records (rows) and fields (columns).
• Record – one row of related data.
• Field – one column; has a name, data type and (optional) sub‑type.
• Primary key – a field (or combination) that uniquely identifies each record (e.g., StudentID).
• Foreign key – a field that references the primary key in another table (e.g., ClassID in Students).
• Field types & sub‑types –

  • Text (Short Text, Long Text)
  • Number (Integer, Long Integer, Double)
  • Currency (stores monetary values, automatically formats with a currency symbol)
  • Percentage (stores a fraction, displays with %)
  • Date/Time (different display formats – short, long, custom)
  • AutoNumber (system‑generated unique number)
  • Yes/No (Boolean)

• Display formats – control how a field is shown (e.g., £#,##0.00 for currency, dd/mm/yyyy for dates).
• Query – a saved request that selects, sorts, filters and can calculate new fields.

2. Importing and Exporting Data

Most IGCSE exams require you to know how to bring external data into a database and how to save a report for later use.

2.1 Importing a CSV/TXT File (Microsoft Access example)

1. Open the database and choose External Data → New Data Source → From File → Text File.
2. Browse to the CSV/TXT file and click Import.
3. In the Import Wizard:

   • Confirm the delimiter (comma, tab, semicolon).
   • Check First Row Contains Field Names if appropriate.
   • Assign the correct data type/sub‑type to each column (e.g., Currency for price, Date/Time for dates).

4. Choose a destination table name (or create a new one) and finish.

2.2 Exporting a Report (PDF or CSV)

• In Print Preview click Export → PDF or XPS to create a non‑editable version.
• For raw data, use External Data → Export → Text File and follow the wizard.

3. Building Queries – The Backbone of a Report

3.1 Query‑building Checklist

3.2 Calculated‑Field Syntax in Different Tools

• Microsoft Access / LibreOffice Base (SQL view):

  SELECT QuantitySold, UnitPrice, QuantitySold*UnitPrice AS TotalSale
  FROM Sales
  WHERE SaleDate >= #01/01/2024#;

• MySQL Workbench:

  SELECT QuantitySold,
  UnitPrice,
  QuantitySold*UnitPrice AS TotalSale
  FROM Sales
  WHERE SaleDate >= '2024-01-01';

4. Designing Forms – Capturing Data Correctly

Although the exam focuses on reports, a solid understanding of form design is required for the “data‑validation” part of the syllabus.

4.1 Typical Form Controls

• Text box – free‑form entry (e.g., student name).
• Combo box – drop‑down list of predefined values (e.g., class names).
• Option group (radio buttons) – mutually exclusive choices (e.g., gender).
• Check box – Yes/No (Boolean) values.
• Command button – runs a macro or opens another form/report.

4.2 Validation Rules (examples)

4.3 Why Validation Matters

• Prevents entry of incorrect or inconsistent data.
• Reduces the need for later clean‑up before reporting.
• Meets the syllabus requirement for “data‑validation routines”.

5. Designing a Complete Report

5.1 Report Sections (standard layout)

• Report Header – title, date generated, optional logo.
• Page Header – full column headings (repeat on every page).
• Detail Section – one line per record returned by the query.
• Report Footer – grand totals, summary text, signatures.
• Page Footer – page number, confidentiality notice.

5.2 Step‑by‑Step Procedure (Microsoft Access style; equivalents exist in Base, MySQL‑Front‑End tools)

1. Open the database and run the query you built in Section 3.
2. Choose Create → Report Wizard (or Report → Design View for full control).
3. Select the query as the data source.
4. Move the required fields into the “Selected Fields” box, arranging them in the order you want them to appear.
5. Specify any grouping (e.g., by ProductCategory) and sorting options.
6. Choose a layout:

   • Tabular – best for numeric data and totals.
   • Columnar – useful for one‑record‑per‑page forms.

7. Select a visual style (simple shading, readable fonts).
8. Click Finish. The report opens in Design view for fine‑tuning.
9. In Design view:

   • Add a Report Header text box with the title, e.g. 2024 Sales Report, centre it and increase the font size.
   • In the Page Header, type full column headings (e.g., “Quantity Sold”, “Unit Price (£)”). Use the Auto‑Label feature if you set the Control Source to the field name.
   • Adjust column widths so the longest expected entry fits; enable Can Grow for text fields that may wrap.
   • Set numeric columns to Right‑Align and apply a numeric format:

     • Currency: £#,##0.00
     • Percentage: 0.00%
     • General Number: #,#0

   • To show a grand total, add a text box to the Report Footer and set its Control Source to =Sum([TotalSale]). Prefix it with a label “Grand Total (£)”.
   • Insert a Page Footer with Page [Page] of [Pages] and, if required, a confidentiality notice.

10. Switch to Print Preview and verify:

    • No truncated data – all text fully visible.
    • Consistent fonts, sizes, shading, and alignment.
    • Correct totals and full column headings on every page.
    • Page numbers and date are present.

11. Save the report (e.g., rpt_Sales2024) and then run, print or export as required.

5.3 Example Report Layout (Sales Report)

Notice the full column headings – “Quantity Sold” instead of “Qty”. This satisfies the syllabus requirement that “labels are shown in full”.

6. Calculated Fields and Totals – Theory and Practice

• Creating a calculated field in a query (Access/Base):

  TotalSale: [QuantitySold] * [UnitPrice]

• Same calculation in MySQL:

  SELECT QuantitySold, UnitPrice, QuantitySold*UnitPrice AS TotalSale FROM Sales;

• Displaying a grand total in a report footer:

  =Sum([TotalSale])

  Place this expression in the Control Source** of a text box in the Report Footer.

• Mathematical representation:

  \$\text{Grand Total} = \sum{i=1}^{n} (\text{Quantity Sold}i \times \text{Unit Price}_i)\$

  where n = number of displayed records.

7. e‑Safety & Data‑Protection

• Before printing or sharing a report, verify that it does not contain unauthorised personal data (e.g., full addresses, ID numbers) unless the exam question explicitly requires them.
• Use password protection on the database file:

  • Access: File → Info → Encrypt with Password
  • Base: File → Properties → Set Password

• When exporting to PDF or CSV, double‑check that column headings do not reveal sensitive information.
• Comply with the UK Data Protection Act / GDPR principles – keep data secure, limit access, and delete unnecessary copies after the exam.

8. Common Mistakes and How to Avoid Them

• Missing or abbreviated labels – always use full headings; check the Page Header after design.
• Truncated data – widen columns, enable Can Grow, or wrap text.
• Incorrect totals – use built‑in aggregate functions (Sum, Avg, Count) rather than manual addition.
• Inconsistent numeric alignment – set Text Align = Right for every numeric control.
• Over‑crowded pages – use appropriate margins, limit shading, and consider grouping to reduce repetition.
• Data‑protection oversight – run a final “privacy check” before printing or exporting.

9. Checklist for a Syllabus‑Compliant Report

1. Report title: centred, larger font, descriptive.
2. All required fields from the query appear in the Detail section.
3. Every column has a full, clear label that repeats on each page (Page Header).
4. Numeric fields are formatted as Currency/Percentage/Number with two decimal places and are right‑aligned.
5. Grand totals or subtotals are correct, labelled, and placed in the appropriate footer.
6. Page Header, Page Footer (page number), Report Header/Footer (date, confidentiality note) are present.
7. No data is truncated; column widths are sufficient for the longest expected entry.
8. Fonts, sizes, and shading are consistent throughout the report.
9. e‑Safety check completed – no unauthorised personal data displayed; file is password‑protected if required.
10. Report has been previewed, saved, and printed or exported as the exam question specifies.

10. Suggested Flowchart for Hand‑outs