ADUVIE

ADUVIE INTERNATIONAL SCHOOL | HIGH SCHOOL ICT

Dual-Board Mastery: Cambridge IGCSE 0417 & WASSCE Digital Technology

0% Complete

WEEK 05 CURRICULAR SUITE

Relational Databases II: Complex Queries & Grouped Reports

Syllabus Alignment: Cambridge 0417 Unit 18; WASSCE Computer Studies (Database Operations)
Mission Objective: Construct multi-criteria database queries using boolean logic, wildcards, and calculated fields, and generate professional grouped reports with summary statistics adhering to exam layout rules.

🎥 Video Lessons 💻 Practical Work ⚙️ Practice Tool 🧠 Notes 🃏 Key Words 🕵️ Common Mistakes 📝 Quiz 🎯 Self-Check

🎥 Video Lessons

Cambridge IGCSE · WAEC · Core Theory

Topic: Cambridge 0417 Paper 2: Complex Queries, Calculated Fields & Tabular Reports | Duration: 16:45
What to look out for: Multi-table query design using wildcard Like '*Creative*', calculated runtime fields (Retail: [Price]*1.2), and 1-page landscape report layout.

⚙️ Hands-On Practice Tool

Interactive

Test Cambridge 0417/21 criteria: (Suitability = 'Gaming' OR Like '*Creative*') AND Read > 2200. Watch calculated field Retail: [Price] * 1.2 update.

Suitability Filter: Read Speed Threshold:

Drive_Code	Model	Suitability	Read (MB/s)	Base Price	Calculated Retail (Price * 1.2)

Matching Records: 34 Records Found (Fits 1 page landscape!)

🧠 Notes & Key Concepts

Theory Summary

🔹

• Query Logic: `AND` criteria are placed on the SAME row in Query Design view (both conditions must be true). `OR` criteria are placed on DIFFERENT rows (either condition can be true).

🔹

• Wildcards: Asterisk `*` matches any sequence of characters (e.g. `Like "Eng*"` matches 'English', 'Engineering'). Question mark `?` matches a single character.

🔹

• Calculated Fields: Syntax is `NewFieldName: [ExistingField] * 1.2`. Field names must be enclosed in square brackets `[ ]`.

🔹

• Report Layout: In Cambridge exams, reports must fit on a specified number of portrait/landscape pages, display no truncated text (`####` or cut-off words), show group subtotals and grand totals, and display candidate details in header or footer.

★ Cambridge / WAEC Model Scenario:

Write the exact query design criteria to find cars manufactured between 2018 and 2022 that are either 'Blue' or 'Silver'.

💡 Worked Solution & Rationale:

In Query Design View:
Field: Year -> Criteria row: `>=2018 And <=2022`
Field: Color -> Criteria row: `"Blue"`
OR row below:
Field: Year -> Criteria row: `>=2018 And <=2022`
Field: Color -> Criteria row: `"Silver"`

🃏 Key Words — Click a Card to Reveal the Definition

6 Terms

Card 01

Wildcard (*)

👆 Click / Tap to Reveal Definition

Definition 01

A symbol used in query criteria representing any sequence of characters (e.g. Like '*Creative*').

Card 02

Calculated Field

👆 Click / Tap to Reveal Definition

Definition 02

A dynamic query column created at runtime using a formula referencing other fields (e.g. Retail: [Price] * 1.2).

Card 03

Data Truncation

👆 Click / Tap to Reveal Definition

Definition 03

When data or headers are partially cut off or displayed as #### due to narrow column widths in reports.

Card 04

Report Header

👆 Click / Tap to Reveal Definition

Definition 04

Section of a database report that prints once at the very beginning of the entire report (displays title).

Card 05

Page Footer

👆 Click / Tap to Reveal Definition

Definition 05

Section of a database report that prints at the bottom of every page (displays page numbers, candidate info).

Card 06

Group Header

👆 Click / Tap to Reveal Definition

Definition 06

Displays the category name at the beginning of each group of records when data is grouped.

🕵️ Common Exam Mistakes — Can You Spot the Error?

Examiner Trap

⚠️ Candidate Error Trap:

A candidate placed `"Red"` on the Criteria row and `"Blue"` on the same row under the `Color` field to find red and blue cars.

🎯 Your Detective Mission:

Explain what output this query produces and how to fix it.

📝 Quiz — Test Yourself (10 Questions)

Instant Marking & Explanation

1. Which query operator searches for text beginning with 'North'?

A) `= "North"`

B) `Like "North*"`

C) `In ("North")`

D) `Between "North"`

Mark Scheme Rationale: Asterisk `*` wildcard searches for any trailing characters.

2. Where are `AND` conditions placed in Microsoft Access Query Design Grid?

A) On different rows

B) On the same Criteria row

C) In the Sort row

D) In the Show row

Mark Scheme Rationale: `AND` conditions sit on the same criteria row.

3. What is the correct syntax for a calculated query field calculating VAT at 7.5% on Price?

A) `VAT = Price * 0.075`

B) `VAT: [Price] * 0.075`

C) `[VAT]: Price * 0.075`

D) `VAT -> Price * 7.5%`

Mark Scheme Rationale: Syntax is `FieldName: [SourceField] * Expression`.

4. What causes `####` to appear in a numeric report field?

A) Division by zero

B) The column width is too narrow to display the number

C) Missing primary key

D) Corrupted data

Mark Scheme Rationale: Hashes indicate the column width is insufficient.

5. Where in a report structure should the overall total count of all records be placed?

A) Page Header

B) Group Header

C) Report Footer

D) Detail Section

Mark Scheme Rationale: Grand totals belong in the Report Footer.

6. Which aggregate function calculates the mean value of a numeric field?

A) `Total()`

B) `Avg()`

C) `Sum()`

D) `Mean()`

Mark Scheme Rationale: `Avg()` calculates the arithmetic mean.

7. What orientation is standard for database reports with 8 or more fields?

A) Portrait

B) Landscape

C) Square

D) Vertical

Mark Scheme Rationale: Landscape provides width to display 8+ fields without data truncation.

8. Which query criteria finds all dates before January 1, 2025?

A) `<#01/01/2025#`

B) `>#01/01/2025#`

C) `=#01/01/2025#`

D) `Not #01/01/2025#`

Mark Scheme Rationale: Dates in Access are enclosed in hashes `#`.

9. What section of an Access report repeats at the top of every single printed page?

A) Report Header

B) Page Header

C) Group Header

D) Detail

Mark Scheme Rationale: The Page Header repeats at the top of every printed page.

10. Which feature organizes report data into categorized clusters (e.g. by Department)?

A) Filtering

B) Grouping

C) Sorting

D) Indexing

Mark Scheme Rationale: Grouping clusters records based on a common field value.

Your Score: 0 / 10 (0%)

Answer the questions above to see your score.

🎯 How Did You Find This Week?

Self-Check

1. Rate your understanding of Week 05 (Relational Databases II: Complex Queries & Grouped Reports):

🏆 Got it — Exam Ready

⚖️ Mostly — Needs More Practice

🚨 Not Yet — Need Help

2. Next step: Open your YEAR 12 ICT & DIGITAL TECHNOLOGY — LIVING EXAM TRACKER.xlsx on Google Classroom and update your score for this week.