Year 10 · ICT · Spring 1

Effects of IT and ICT applications; databases · Lesson 5 of 5

Practical: queries, reports and charts

Practical18.2 Manipulate data: calculations, sorting and queries18.3 Present data: reports16.2 Label and format a chart

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Success criteria

You have got it when you can say:

  • 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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last lesson

    A microprocessor-controlled washing machine…

    A Cannot be programmedB Needs someone to watch itC Frees the owner's time

    C. Frees the owner's time

  2. Last unit

    A .pdf is used because…

    A Anyone can edit itB It is the smallest fileC It looks the same on every computer

    C. It looks the same on every computer

  3. Earlier this year

    Non-contiguous data means…

    A A single columnB Numbers onlyC Ranges that are not next to each other

    C. Ranges that are not next to each other

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Key words (1 of 6)

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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Key words (2 of 6)

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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Key words (3 of 6)

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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Key words (4 of 6)

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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Key words (5 of 6)

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.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Key words (6 of 6)

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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 1 of 19 · part 1 of 3

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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 1 of 19 · part 2 of 3

Calculated fields and calculated controls (continued)

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])

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 1 of 19 · part 3 of 3

Calculated fields and calculated controls (continued)

?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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 2 of 19 · part 1 of 3

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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 2 of 19 · part 2 of 3

Sum, average, maximum, minimum, count (continued)

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

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 2 of 19 · part 3 of 3

Sum, average, maximum, minimum, count (continued)

!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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 3 of 19 · part 1 of 3

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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 3 of 19 · part 2 of 3

Sort on one or more fields (continued)

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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 3 of 19 · part 3 of 3

Sort on one or more fields (continued)

!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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 4 of 19 · part 1 of 2

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.

18.2 Manipulate data: calculations, sorting and queries · The explanation · step 4 of 19 · part 2 of 2

Read a query grid: a simple search (continued)

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

The explanation · 15 more steps

More on the lesson page

The lesson page has the rest of the explanation, step by step:

  • Comparison operators
  • AND: every condition must be true
  • OR: any condition can be true
  • NOT, LIKE and wildcards
  • Queries across two tables, and evidence
  • Reports: tabular or columnar
  • Field selection, field order and labels
  • Titles, headers and footers
  • Align numbers and format them
  • A finished report with calculated controls
  • How report marks are lost
  • Titles, axis titles and labels
  • Legend: show it or hide it
  • Data labels: values and percentages
  • Number format, axis scale and increments

Open the lesson page

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Quick quiz · 1 of 3

ANPR reads…

  1. A Fingerprints
  2. B Barcodes
  3. C Number plates

C. Number plates

Automatic number plate recognition, in car parks and on roads.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Quick quiz · 2 of 3

In automatic stock control, the stock level drops when…

  1. A The barcode is scanned at the till
  2. B The shop closes
  3. C The manager counts

A. The barcode is scanned at the till

When stock reaches the reorder level the system orders more.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Quick quiz · 3 of 3

A spreadsheet weather model is used because…

  1. A It needs no data
  2. B It is cheaper and safer than real experiments
  3. C It is always exactly right

B. It is cheaper and safer than real experiments

Models predict; they are only as good as their data and rules.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. EFTPOS means…

    A Sending an emailB Paying by card at the tillC Posting a cheque

    B. Paying by card at the till Electronic funds transfer at point of sale.

  2. A foreign key…

    A Links a record to a record in another tableB Is a passwordC Is always unique

    A. Links a record to a record in another table It holds the other table's primary key.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 5 of 5

Homework and the challenge

Homework

The ICT 5, ICT 6 and ICT 18 quizzes.

End-of-unit challenge

End-of-unit test: topics 5 and 6 theory plus a timed database task.