Skip to the page
Year 10 · ICT · Spring 1 · Effects of IT and ICT applications; databases

Practical: queries, reports and charts

PracticalPresent ▶

What this lesson covers

18.2 Manipulate data: calculations, sorting and queries

Cambridge IGCSE ICT 0417 · Topic 18: Databases · Paper 2

18.3 Present data: reports

Cambridge IGCSE ICT 0417 · Topic 18: Databases · Paper 2

16.2 Label and format a chart

Cambridge IGCSE ICT 0417 · Topic 16: Graphs and charts

You will learn to

  • Write query criteria with two conditions, using AND or OR.
  • Sort records and create a calculated field.
  • Produce a report with only the fields asked for, a calculated field and a total.
  • Create a chart with a title, axis titles and the right labels.

Success criteria

  • I can build a query with two criteria and show it as evidence.
  • I can add a calculated field and total it in a report.
  • I can make a chart with every label the task asks for.
The syllabus points for these sections
  • Create calculated fields in queries and calculated controls in reports and forms.
  • Use sum, average, maximum, minimum and count.
  • Sort on one or more fields, ascending or descending.
  • Write query criteria with =, >, <, >=, <=, <> and AND, OR, NOT.
  • Use LIKE with the wildcards * and ?.
  • Give clear evidence of every query you build.
  • Produce a report in tabular or columnar layout.
  • Select only the fields asked for, in the order asked for.
  • Add titles, headers and footers.
  • Right-align numbers and line up decimal points.
  • Format numbers as currency, decimals or percentages, and change labels.
  • Add a chart title, legend, axis titles and category labels.
  • Add data labels as values or percentages.
  • Format numbers with decimal places and currency.
  • Set axis minimum, maximum and increments.

Key words

Say each word, then read what it means. Every one is in the glossary too.

Database
An organised store of data that can be searched, sorted and reported on.
Table
A set of records about one kind of thing, such as members or bookings.
Record
One row of a table: all the data about one item.
Field
One column of a table: one piece of data stored for every record.
Field type
The kind of data a field holds: text, numeric, date/time or Boolean.
Display format
How a value looks on screen or paper, without changing the stored value.
Query
A search that shows only the records that meet given criteria.
Criteria
The conditions a record must meet to appear in a query result.
Wildcard
A symbol in a search pattern: * means any number of characters, ? means exactly one.
Calculated field
A field in a query that is worked out from other fields, not typed in.
Calculated control
A box on a report or form that shows a calculation, such as a total.
Tabular layout
A report layout with records in rows under column headings.
Columnar layout
A report layout that shows one record at a time, with each label beside its value.
Chart
A picture of data, such as bars, lines or slices, that makes patterns easy to see.
Pie chart
A circle split into slices, each showing a part of the whole.
Bar chart
Bars of different lengths for comparing categories; vertical bars are also called a column chart.
Line graph
Points joined by lines to show change over time.
Data series
One set of values plotted on a chart.
Legend
The key that says what each colour or pattern on a chart means.
Axis title
Text naming what an axis measures, with its units.
Category labels
The names along the category axis, such as months or products.
Data label
A value or percentage shown on a bar, point or slice.
Axis minimum / maximum
The lowest and highest values shown on the value axis.
Increment (major unit)
The step between values on an axis, such as every 10.

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

18.2 Manipulate data: calculations, sorting and queries

Calculated fields and calculated controls

A calculated field is a new field in a query, worked out from other fields. It is not typed in and not stored in the table.

A calculated control is a box on a report or form that shows a calculation, such as a total.

In most database software you give the new field a name, a colon, then the formula. Field names go in square brackets.

e.g.Example
Booking 101: Hours 1.5, Rate €12.00 Cost = 1.5 × 12.00 = €18.00 Booking 103: Hours 2, Rate €11.00 Cost = 2 × 11.00 = €22.00 All eight costs: 18.00, 4.50, 22.00, 4.50, 8.00, 12.00, 24.00, 11.00
Calculated field in a query:
Cost: [Hours]*[Rate]

Calculated control in a report footer:
=Sum([Cost])
?Why?

Calculating, not storing, keeps the answer right. If someone corrects the hours, the cost updates by itself.

!Watch out

Spell the field names exactly as in the table. [Hour] instead of [Hours] makes the software ask for a value, and the answer is wrong.

Sum, average, maximum, minimum, count

These five functions summarise many records into one value. You use them in reports (calculated controls) or in queries (a totals row).

Sum adds up. Average adds up and divides by how many. Max finds the biggest. Min finds the smallest. Count counts the records.

FunctionFormula typedResult for all 8 bookings
Sum=Sum([Cost])€104.00
Average=Avg([Cost])€13.00
Maximum=Max([Cost])€24.00
Minimum=Min([Cost])€4.50
Count=Count([BookingID])8
e.g.Example
Sum: 18 + 4.50 + 22 + 4.50 + 8 + 12 + 24 + 11 = 104.00 Average: 104.00 ÷ 8 = 13.00 Max: 24.00 (booking 107) Min: 4.50 (bookings 102 and 104) Count: 8 records
!Watch out

Put a total in the report footer, not the page footer. A page footer total often shows an error or only that page's records.

Sort on one or more fields

Ascending means A to Z, smallest to largest, earliest date to latest. Descending is the reverse.

You can sort on two fields. The first sort field decides the main order. The second only matters when the first field has equal values.

In most query grids the software sorts from left to right. Put the main sort field to the left of the second one.

e.g.Example
Task: sort by Activity ascending, then by Cost descending. Padel 107 €24.00 Padel 101 €18.00 Padel 106 €12.00 Pilates 105 €8.00 Swimming 102 €4.50 Swimming 104 €4.50 Tennis 103 €22.00 Tennis 108 €11.00
?Why?

Within the three Padel bookings, the activity is the same, so the second sort (Cost, largest first) decides their order.

!Watch out

If Cost sits to the left of Activity in the grid, the software sorts by Cost first. The printout is then in the wrong order and loses the mark.

Read a query grid: a simple search

A query asks the database a question and shows only the matching records. Most software gives you a design grid.

Each column is one field. The rows are: Field, Table, Sort, Show, Criteria and Or.

Criteria is the condition a record must meet. Show decides if the field appears in the result.

Task: find all Padel bookings, by date. The software adds quote marks around text, so "Padel" is fine.

FieldBookingIDActivityBookDateCost: [Hours]*[Rate]
TableBookingsBookingsBookings
SortAscending
Show✓✓✓✓
Criteria"Padel"
Or
e.g.Example
Result (3 records): 101 Padel 06/10/2026 €18.00 106 Padel 09/10/2026 €12.00 107 Padel 10/10/2026 €24.00

Comparison operators

Comparison operators compare a field with a value. They work on numbers, currency and dates.

= equal to. > greater than. < less than. >= greater than or equal to. <= less than or equal to. <> not equal to.

For dates, "greater than" means later. Some software puts # signs round a date for you. That is normal.

CriteriaFieldMatching bookings
>10Cost101, 103, 106, 107, 108
<=8Rate102, 104, 105
>=08/10/2026BookDate104, 105, 106, 107, 108
<>"Swimming"Activity101, 103, 105, 106, 107, 108
2Hours103, 107
!Watch out

Watch the difference between > and >=. ">08/10/2026" leaves out bookings 104 and 105, which are on 8 October. Read whether the task says "after" or "on or after".

AND: every condition must be true

AND means a record must meet all the conditions. AND makes the result smaller.

In a query grid, conditions on the same Criteria row are joined with AND.

Task: find Padel bookings that are not paid.

FieldBookingIDActivityPaid
TableBookingsBookingsBookings
Sort
Show✓✓✓
Criteria"Padel"No
Or
e.g.Example
Padel bookings: 101, 106, 107 Of these, Paid = No: only 106 Result: 1 record (106, Padel, No) A range on one field also uses AND: Cost criteria: >=10 And <=20 Result: 101 (€18.00), 106 (€12.00), 108 (€11.00)
?Why?

Cost cannot be both above 20 and below 10, so AND inside one cell gives a range. Some software also accepts Between 10 And 20.

OR: any condition can be true

OR means a record needs to meet at least one condition. OR makes the result bigger.

In a query grid, conditions on different rows (Criteria and Or) are joined with OR. You can also type "Padel" Or "Tennis" in one cell.

Each row is read as a complete condition on its own. This matters when you mix AND with OR.

FieldBookingIDActivityPaid
TableBookingsBookingsBookings
Sort
Show✓✓✓
Criteria"Padel"No
Or"Tennis"No
e.g.Example
Task: unpaid Padel or Tennis bookings. Row 1: Padel AND No → 106 Row 2: Tennis AND No → 103 Result: 103 and 106 Wrong grid: "No" typed only on row 1. Row 1: Padel AND No → 106 Row 2: Tennis (any Paid) → 103, 108 Result: 103, 106, 108. Booking 108 is paid, so this is wrong.
!Watch out

When you mix AND and OR, repeat the shared condition on every row. This is one of the most common lost marks in Paper 2.

NOT, LIKE and wildcards

NOT finds records that do not match. Not "Swimming" gives the same result as <>"Swimming".

LIKE searches for a pattern instead of an exact value. It uses wildcards: symbols that stand for unknown characters.

The `*` wildcard stands for any number of characters, including none. The `?` wildcard stands for exactly one character.

Try it below. The toy uses a different table: a sports centre's bookings. Press the Member LIKE S* example, then type Not in front of Like "S*". Watch the matching and non-matching records swap. Then try Like "?a*".

CriteriaFieldMatchesBookings / members found
Like "P*"ActivityStarts with PPadel and Pilates: 101, 105, 106, 107
Not Like "P*"ActivityDoes not start with PSwimming and Tennis: 102, 103, 104, 108
Like "*ing"ActivityEnds with ingSwimming: 102, 104
Like "?a*"LastNameSecond letter is aGarcía, Martín, Navarro (M01, M04, M05)
?Why?

In "?a*" the ? uses up exactly the first letter, so the a must be second. Ruiz and López fail because their second letters are u and ó.

!Watch out

Some database software uses % and _ instead of * and ?. Cambridge uses * and ?. If you type "P*" without LIKE, many programs search for a real star.

There is something to try for this step in the IGCSE library.

Queries across two tables, and evidence

Because the tables are related, one query can show fields from both. Add both tables to the query; the relationship joins them on MemberID.

Task: show the name, activity and cost of every unpaid booking, with a charge after discount.

Evidence: take a screenshot of the design grid showing all criteria in full, and a screenshot or printout of the result.

FieldFirstNameLastNameActivityPaidCharge: [Hours]*[Rate]*(1-[Discount])
TableMembersMembersBookingsBookings
SortAscending
Show✓✓✓✗✓
CriteriaNo
Or
e.g.Example
Unpaid bookings: 103, 105, 106, sorted by LastName Carmen López, Pilates: 1 × 8.00 × (1 − 0.3) = €5.60 Diego Martín, Tennis: 2 × 11.00 × (1 − 0) = €22.00 Sofía Navarro, Padel: 1 × 12.00 × (1 − 0.2) = €9.60
?Why?

Paid is used to search but is not shown, so Show is unticked. The task decides which fields appear.

!Watch out

Common lost marks: criteria cut off in a narrow column, a typing error such as "Paddel", showing fields the task did not ask for, or no screenshot of the criteria at all. The examiner can only mark what is visible.

18.3 Present data: reports

Reports: tabular or columnar

A report is a formatted printout of data from a table or query. It is made for people to read, not for typing data in.

Tabular layout shows records as rows under column headings, like a table. It fits many records on a page.

Columnar layout shows one record at a time, with each label next to its value, down the page. It suits a record card.

e.g.Example
Tabular BookingID Activity Cost 101 Padel €18.00 103 Tennis €22.00 Columnar BookingID: 101 Activity: Padel Cost: €18.00
?Why?

Pick the layout the task names. If none is named, tabular is usually clearer for a list of records.

Field selection, field order and labels

Base the report on the query that holds the right records. Then add only the fields the task lists.

Put the fields in the exact order the task gives, from left to right.

A label is the text heading above or beside a field. You can change it to plain words, such as "Booking date" instead of BookDate, if the task allows.

Every label and every value must be shown in full. Nothing may be cut off.

!Watch out

Report wizards often shorten labels or squash columns, so "Activity" shows as "Activ". Widen every control and check print preview before printing.

Titles, headers and footers

The report title goes in the report header. It appears once, at the top of page 1.

The page header repeats at the top of every page. It usually holds the column labels.

The page footer repeats at the bottom of every page: page numbers, date. The report footer appears once at the end: totals and averages.

Cambridge often asks for your name, centre number and candidate number in the header or footer. That is how your printout is identified.

e.g.Example
Report header: Padel and Tennis Bookings, October 2026 Page header: BookingID | Booking date | Activity | Hours | Cost Detail: one line per booking Report footer: Total cost, Average cost Page footer: Ana Pérez 12345 0001 | Page 1 of 1
!Watch out

Type the title exactly as given, with the same spelling and capital letters. Extra or missing words lose the mark.

Align numbers and format them

Right-align numbers so the units, tens and hundreds line up. Then values are easy to compare.

Decimal alignment means the decimal points sit in one column. Setting every value to the same number of decimal places does this.

Use the format the task asks for: currency with a symbol, a set number of decimal places, or a percentage.

Left-aligned    Right-aligned, 2 dp
€4.5              €4.50
€22              €22.00
€8                €8.00
€104            €104.00
?Why?

When digits line up, the eye compares numbers by their width. €104.00 clearly looks bigger than €8.00.

!Watch out

A label above a right-aligned number should also be right-aligned. Otherwise the heading looks like it belongs to the next column.

A finished report with calculated controls

This report uses the query for Padel or Tennis bookings, sorted by date ascending. It has a title, labels in full, right-aligned numbers and totals in the report footer.

Hours are shown to 1 decimal place. Cost is currency in euros with 2 decimal places.

BookingIDBooking dateActivityHoursCost
10106/10/2026Padel1.5€18.00
10307/10/2026Tennis2.0€22.00
10609/10/2026Padel1.0€12.00
10710/10/2026Padel2.0€24.00
10810/10/2026Tennis1.0€11.00
Total7.5€87.00
Average€17.40
Number of bookings5
e.g.Example
Total cost: =Sum([Cost]) → 18 + 22 + 12 + 24 + 11 = €87.00 Average: =Avg([Cost]) → 87.00 ÷ 5 = €17.40 Count: =Count([BookingID]) → 5 Total hours: =Sum([Hours]) → 1.5 + 2 + 1 + 2 + 1 = 7.5
?Why?

Each calculated control has a label beside it. A number on its own, such as 87, means nothing to a reader.

How report marks are lost

Most report marks are for small details. Check each one in print preview before you print.

Use landscape orientation if the fields do not fit across a portrait page. The report should be one page wide.

CheckLost mark if…
Right recordsThe query criteria were wrong, so extra or missing rows appear
Right fields, right orderA field is missing, extra, or in the wrong place
Full labels and dataAny heading or value is cut off
Sort orderRecords are not in the order asked for
TitleMissing, misspelt, or not in the report header
Number formatsWrong currency symbol or wrong number of decimal places
AlignmentNumbers are not right-aligned
TotalsA calculated control is missing, wrong, or has no label
IdentificationName, centre number or candidate number missing
!Watch out

Printing the table instead of a report loses most of the report marks. A report needs a title and formatted layout.

16.2 Label and format a chart

Titles, axis titles and labels

Chart title: says what the chart shows. Type it exactly as the task gives it.

Axis titles: name what each axis measures, with units, such as "Number of hires" or "Price (€)".

Category labels (axis labels): the names along the axis, such as Road, Mountain, Electric. All must be fully visible.

e.g.Example
Vertical bar chart: Title: Bike Hire in May Category axis title: Bike type Value axis title: Number of hires Category labels: Road, Mountain, Electric
!Watch out

Category labels that are cut off or overlap lose the mark. Make the chart wider or rotate the labels so every word shows in full.

Legend: show it or hide it

A legend is the key that says what each colour means.

With one data series in a bar chart, a legend adds nothing, so tasks often say "do not display a legend".

With two or more series, or in a pie chart without category labels on the slices, the legend is needed.

?Why?

Every item on a chart should help the reader. A legend that says only "Series1" confuses.

!Watch out

A legend showing "Series1" means the data was selected without its header. Reselect including the header cell, or rename the series.

Data labels: values and percentages

Data labels show the number on each bar or slice.

A task may say "show the values" or "show the percentages". On a pie chart you can show the category name and the percentage on each slice.

Values are the raw numbers. Percentages are each value as a share of the total.

e.g.Example
Task: Display the percentage and category name for each segment. Result on slices: Road 30%, Mountain 20%, Electric 50%.
!Watch out

Showing values when the task said percentages, or both when it said one, loses the mark.

Number format, axis scale and increments

Format value axis numbers as asked: decimal places, or currency, such as €0.00.

The axis minimum and maximum set where the value axis starts and ends. The increment (major unit) is the step between gridlines, such as every 10.

A typical task: "Set the value axis from 0 to 200 with increments of 25."

e.g.Example
Hires range from 60 to 150. Task: minimum 0, maximum 160, increment 20. Axis reads 0, 20, 40, 60, 80, 100, 120, 140, 160. Check: 160 ÷ 20 = 8 steps, so 9 labels.
?Why?

Starting the axis at 0 shows differences fairly. Starting at 50 would make 60 look tiny next to 150, which misleads the reader.

!Watch out

The software picks automatic values. If the task gives numbers, you must type them in. The automatic value is almost never what Cambridge asks for.

Study it

Open the study page and go one step at a time.

ICT topic 5

The effects of using IT

Step by step, with exam questions and a quiz.

ICT topic 6

ICT applications

Step by step, with exam questions and a quiz.

ICT topic 18

Databases

Step by step, with exam questions and a quiz.

ICT topic 16

Graphs and charts

Step by step, with exam questions and a quiz.

Topic 18 · Year 7

Data, spreadsheets and AI

Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, AI learns patterns from examples.

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.