AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Ask the database

Week 19 · Unit 7.4: The power of data · 3 lessons
3

Highlight what matters

Computing 7: Unit 7.4, pp. 112–149
GoalWrite conditional formatting rules for a database form or report, and test which records they highlight.
New words
control · an item on a database form, such as a text box or a labeldesign view · the view where the layout and rules of a form or report can be changedrule · a condition and a format that is used when the condition is truehighlight · to make important data stand out, for example with colour or bold text
Explanation

Organisers often need to see the important records at a glance. Conditional formatting can highlight data on input forms and on reports. You make the rule in design view by choosing the control, the condition and the format. The format is applied only to records for which the condition is TRUE. Scroll through the records afterwards to test the rule. Do not use too many colours, because the page gets confusing. Some people cannot tell colours apart, so make highlighted data bold as well as coloured.

Part of the ruleExample
Control (field)Genre
Conditionequal to Science
Formatbold, blue fill
Worked examples
ABCDEF
1BookIDTitleGenrePagesYearOnLoan
21River MapsAdventure2102018Yes
32The Quiet KiteStory962020No
43Space AtlasScience3202021Yes
54Tiny MachinesScience1482019No
65Desert WindAdventure2752016No
76The Baking ClubStory1842022Yes
87Rock and RainScience4122015No
98Night TrainAdventure3502020Yes
The library wants Science books to stand out. The rule is Genre = Science, with a blue fill. The shaded rows are the records for which the condition is TRUE. (Ignore the letters and numbers around the edge of the grid. They are part of the picture, not of the table.)
ABCDEF
1BookIDTitleGenrePagesYearOnLoan
21River MapsAdventure2102018Yes
32The Quiet KiteStory962020No
43Space AtlasScience3202021Yes
54Tiny MachinesScience1482019No
65Desert WindAdventure2752016No
76The Baking ClubStory1842022Yes
87Rock and RainScience4122015No
98Night TrainAdventure3502020Yes
A second rule highlights long books with Pages > 300. Notice that a record can match one rule, both rules or neither. Space Atlas (320 pages) and Rock and Rain (412 pages) are Science books that are long, so both rules apply to them. Night Train (350 pages) matches only the second rule.
RecordPagesPages > 300
Tiny Machines (before)148FALSE
Tiny Machines (after)340TRUE
Test your rule by changing the data. If Tiny Machines grows to 340 pages after a new edition, the condition Pages > 300 becomes TRUE for that record, and it is highlighted at once. This is how you check that a rule works for new data too.
Class activity

Highlighter test (no computer). Copy the Books table onto paper, or make a table of ten things, such as the age and height of toy animals. Ask a partner for a rule such as ‘Pages less than 150’ or ‘OnLoan equal to Yes’. Colour the matching rows with a highlighter pen or coloured pencil. Then change one value on purpose and say which row changes colour. Finally add a second rule in a different style, such as underlining, and check which rows get both rules.

Practice
1
The rule is Year < 2018, with a red fill. Which records are highlighted? Give their BookID numbers.
ABCDEF
1BookIDTitleGenrePagesYearOnLoan
21River MapsAdventure2102018Yes
32The Quiet KiteStory962020No
43Space AtlasScience3202021Yes
54Tiny MachinesScience1482019No
65Desert WindAdventure2752016No
76The Baking ClubStory1842022Yes
87Rock and RainScience4122015No
98Night TrainAdventure3502020Yes
2
Write a conditional formatting rule (field, condition and format) that makes books that are on loan stand out in orange.
3
Rule A: Pages > 200 in red. Rule B: Genre = Story in blue. Which BookID numbers get rule A? Which get rule B? Does any record get both?
RuleFieldConditionFormat
APages> 200red fill
BGenre= Storyblue fill
4
Why should highlighted records be bold as well as coloured?