Be able to use appropriate headers and footers within a database report including report header, report footer, page header, page footer

Topic 18 – Databases

Learning Objective

Students will be able to:

• Create a simple relational database (tables, fields, data types, primary/foreign keys, relationships).
• Design a data‑entry form and apply basic validation.
• Manipulate data using queries, calculated fields, sorting, searching and filters.
• Produce a well‑structured report that uses the four header/footer sections (report header, report footer, page header, page footer), and export the report in a suitable format (PDF/CSV).
• Explain the terminology used in report design and recognise equivalent terms in the most common database packages.

1. Database Structure – the Foundation (Syllabus 18.1)

A relational database consists of tables that store data in fields (columns). Each field has a data type (Text, Number, Date/Time, Boolean, Currency, etc.).

• Primary key (PK) – a field (or combination of fields) that uniquely identifies each record.
• Foreign key (FK) – a field that links a record to the primary key of another table, establishing a relationship.

Example – Simple sales database

Creating these tables first satisfies the requirement to “create a database structure” before any reports are generated.

2. Form Design – entering data safely (Syllabus 18.1)

Forms provide a user‑friendly way to add, edit or delete records. Key design points:

• Controls – text boxes for Text/Number fields, combo‑boxes for list selection, check boxes for Boolean values.
• Validation rules – e.g. >=0 for UnitsSold, Is Not Null for required fields.
• Tab order – set the logical sequence of fields using the “Tab Order” dialog.
• Layout – align labels and controls, use sections (Header, Detail, Footer) to group related items.

Mini‑exercise: Build a “Sales Entry” form for the Sales table, include a combo‑box that shows ProductName (linked to ProductID) and set a validation rule so that UnitsSold cannot be negative.

3. Data Manipulation – queries and calculations (Syllabus 18.2)

Queries retrieve, calculate, sort and filter data. They can be saved and used as the data source for reports.

3.1 Basic Select Query

SELECT ProductName, UnitsSold, UnitPrice
FROM Products
INNER JOIN Sales ON Products.ProductID = Sales.ProductID;

3.2 Calculated Field in a Query

SELECT ProductName,
UnitsSold,
UnitPrice,
UnitsSold * UnitPrice AS SaleValue
FROM Products
INNER JOIN Sales ON Products.ProductID = Sales.ProductID;

3.3 Sorting

In the query design grid, set the Sort row to “Ascending” for ProductName to list products alphabetically.

3.4 Searching / Filtering

Example criterion to show only high‑volume sales:

UnitsSold > 80

3.5 Using the Query in a Report

Save the query (e.g., qryMonthlySales) and select it as the report’s record source. All calculations, sorting and filters defined in the query will be reflected in the report.

4. Presenting Data – reports, charts and export (Syllabus 18.3)

• Reports – formatted, printable presentations of query results. Include headers/footers, grouping, totals.
• Charts – visual summaries (bar, line, pie). In Access, insert a chart control and bind it to a query; in LibreOffice Base, use the “Chart” wizard.
• Export options – PDF (for submission), CSV (for data exchange), XPS or Word.
• Page‑setup – orientation (portrait/landscape), margins, “Fit to page”, and print‑area settings must be checked before printing.

5. Report Sections – what each header/footer does (Syllabus 18.3)

6. Equivalent Terminology in Common Packages

7. Step‑by‑Step: Build a “Monthly Sales Report” with All Four Sections

1. Prepare the data source

   • Create the Products and Sales tables (as shown in Section 1).
   • Enter the sample data (see the table in Section 1) or import it from a CSV file.
   • Build a query qryMonthlySales that joins the two tables and adds a calculated field SaleValue = UnitsSold * UnitPrice. Sort by ProductName.

2. Open a new report in Design view and set its Record Source to qryMonthlySales.
3. Insert a Report Header

   • Type “Monthly Sales Report”.
   • Add the current month (use the Date() function) and your name.
   • Centre the text, use 14 pt bold Arial, and optionally insert a logo.

4. Insert a Page Header

   • Add three label controls: “Product”, “Units Sold”, “Unit Price”.
   • Set the property “Print on All Pages” (Access) or ensure the header band is marked “Repeat on each page” (Base).
   • Apply a thin bottom border to separate the header from the detail rows.

5. Design the Detail section

   • Drag the fields ProductName, UnitsSold, UnitPrice onto the band.
   • Align columns, use a consistent font (Arial 10 pt).

6. Insert a Page Footer

   • Add a Text Box with the expression ="Page " & [Page] & " of " & [Pages] (Access) or the equivalent in Base.
   • Align the page number to the right margin.

7. Insert a Report Footer

   • Place a label “Total Sales”.
   • Insert a Text Box, set its Control Source to =Sum([SaleValue]), and format as Currency.

8. Optional – Grouping by Product Category

   • Open the “Grouping & Sorting” pane, add a group on Category (if such a field exists).
   • Insert a Group Header for the category name and a Group Footer with =Sum([SaleValue]) to show subtotals.

9. Format & preview

   • Check margins, orientation (portrait for this example), and that all four sections appear as intended.
   • Use Print Preview; adjust “Fit to page” if necessary.

10. Export

    • File → Export → PDF (for submission) or CSV (if the teacher requests raw data).
    • Save the PDF with a clear filename, e.g., MonthlySalesReport_March2025.pdf.

8. Key Points to Remember (Prioritised)

1. Report Header/Footer appear only once – use them for titles, totals, conclusions.
2. Page Header/Footer repeat on every printed page – ideal for column headings and page numbers.
3. Always enable “Print on All Pages” (or “Repeat on each page”) for page‑level sections.
4. Keep fonts, sizes and alignment consistent across all sections for a professional look.
5. Use the correct expression syntax for calculations: =Sum([Field]), =Avg([Field]), =Count([Field]).
6. Preview before printing; adjust page orientation, margins and “Fit to page” settings.
7. Export the final report as a PDF (or CSV) as required by the assessment.

9. Common Mistakes & How to Avoid Them

• Placing the report title in the Page Header – it will repeat on every page; keep titles in the Report Header.
• Forgetting “Print on All Pages” for Page Header/Footer – the section will only appear on the first page.
• Inconsistent fonts or sizes – apply a style or copy‑format to all header/footer controls.
• Incorrect Control Source for totals – remember the equal sign and square brackets (e.g., =Sum([UnitsSold]*[UnitPrice])).
• Not previewing before printing – use Print Preview to catch cut‑off text, missing page numbers or wrong orientation.
• Exporting the wrong file type – check the assessment brief; PDF is usually required for a finished report, CSV for raw data.

10. Summary

Creating a relational database, designing a data‑entry form, manipulating data with queries, and finally presenting the information in a polished report are the core skills required by Cambridge IGCSE ICT 0417. Mastery of the four report sections—report header, report footer, page header, page footer—allows students to produce clear, professional documents that meet both the syllabus specifications and real‑world business expectations.