DATABASES

Designed by Ajiro Ndi

Certified Cambridge ICT Teacher and Examiner
TABLE OF CONTENT

Create a database structure

Manipulate data

Present data
 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
FLAT-FILE DATABASES
CREATE A DATABASE STRUCTURE A flat-file database stores its data in one table, which is organised by rows and
columns. For example, in the following database about teachers, each record
WHAT IS A DATABASE?
(row) in the table contains data about one person. Each column in the table
A database is an organised collection of data. A database program is software contains a field, which has been given a field name, and each cell in that column
which stores and retrieves data in a structured way. This includes the data has the same, predefined data type.
that is stored and the links between the data items. All databases store data
Field name
using a system of files, records and fields: Teacher ID Forename Surname Subject

001 Ajiro Ndi ICT

A field is a single item of data, such as a forename or date of birth. Each 002 Amina Yusuf Maths

field has a field name that is used to identify it within the database. Each 003 Farah Ahmed Physics Records

field contains one type of data, for example numbers, text or a date. 004 Ibrahim Khalid English

A record is a collection of fields, for example, all the information about 005 Hassan Ali Geography

006 Gideon Sunday Chemistry

one person or one item. These may contain different data types.
A file (in database terms) is an organised collection of records, usually
Fields
where all the records are organised so that they can be stored together. A
file can have one or more tables within it.

Although all databases have these three elements in common, there RELATIONAL DATABASES
are two types: A relational database stores data in more than one linked table, stored in a file.
flat-file databases Relational databases are designed so that the same data is not stored many times.
relational databases.

Designed and Presented by Ajiro Ndi 1

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
If the data is split into two tables – one for the teachers and one for the students – that
KEY FIELDS are linked together, it can be stored and retrieved more efficiently, like this:
Each table within a relational database will have a key field. The relationships
Teachers table: Students’ table:
linking the tables use these key fields.
Teacher_ID Forename Surname Subject Student_ID Student_FName Student_SName English maths Science

PRIMARY AND FOREIGN KEY FIELDS AND

KEO
Ajiro

Kelechi
Ndi

Okpara
ICT

Biology
G12345

G12346
Jasmine

James
Hall

Ling
JKW

SJR
AND

AND
KEO

GSD

Most tables will have a primary key field that holds unique data (no two records JKW Jennie Kwong English G12348 Addy Paredes SJR AND KEO

GSD Gideon Sunday Chemistry G12349 Hayley Lemon JKW AND KEO
are the same in this field) and is the field used to identify that record. Some
SJR Sarah Jordan English G12351 Jennie Campbell JKW AND GSD
tables will have one or more foreign key fields. A foreign key field in one data
table stores values from a primary key field in another table. These two tables are linked with a ‘one-to-many’ relationship, because one teacher’s
record is linked to many students’ records. The primary key fields (which must contain
Using the earlier example, if we wanted to add to the table the names of
unique data) are the Student_ID and Teacher_ID.
each student taught by each teacher using a flat-file database, the table
would look like this:
ADVANTAGES AND DISADVANTAGES OF USING FLAT-FILE AND RELATIONAL DATABASES

Teacher_ID Forename Surname Subject Student_ID Student_FName Student_SName There are three common types of changes which can be made to the data contained in a
AND Ajiro Ndi ICT G12345 Jasmine Hall database. Records/data can be added, edited or deleted. Because data is not repeated in a
AND Ajiro Ndi ICT G12346 James Ling
relational database, each change to an item of data or to a record has to be made only
AND Ajiro Ndi ICT G12348 Addy Paredes

AND Ajiro Ndi ICT G12349 Hayley Lemon

once. It is also much easier for users to produce reports from a relational database, where
AND Ajiro Ndi ICT G12351 Jennie Campbell data is held in two or more tables, than from two or more flat-file databases.
KEO Kelechi Okpara Biology G12345 Jasmine Hall Although people often think that it is quicker to search using relational rather than flat-file
KEO Kelechi Okpara Biology G12348 Addy Paredes
databases, it is not always the case. In some cases, where indexed values are used, it can be
KEO Kelechi Okpara Biology G12349 Hayley Lemon
true. It depends on the structure of both databases and the quantity of the data being
JKW Jennie Kwong English G12345 Jasmine Hall

JKW Jennie Kwong English G12349 Hayley Lemon

searched.
JKW Jennie Kwong English G12351 Jennie Campbell You will need to create both flat-file and relational databases, but the data for these will be
GSD Gideon Sunday Chemistry G12346 James Ling provided. You will be using Microsoft Access, which is part of the Microsoft Office suite.
GSD Gideon Sunday Chemistry G12351 Jennie Campbell
When used with a single table Access is a flat-file database, but it can also be a relational
SJR Sarah Jordan English G12346 James Ling

SJR Sarah Jordan English G12348 Addy Paredes

database when used with more than one linked table.

Designed and Presented by Ajiro Ndi 2

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
DATA TYPES AND SUB-TYPES decimal sub-type, which will allow a large number of decimal places, or a
specified restricted number if this is set in the field properties when the
When you create a new database you will set a data type for each field. The data database is created
type tells Access how to store and manipulate the data for each field. You will currency sub-type, which will allow currency formatting to be added to the
usually decide what data type should be used for each field. display. This includes currency symbols and regional symbols. The database
in Access an alphanumeric field is called a text field. The three main types of field does not store these symbols as this would use up valuable storage space
are alphanumeric, numeric and Boolean. date and time sub-type, which stores a date and/or time as a number.
Note:
There are other data types, such as autonumber (which generates unique
While it is listed in the syllabus, in Access ‘percentage’ is not a numeric data sub-
numbers). Some packages, such as Access, have long and short versions of their
type, it is just a formatting option.
data types (for example, long text and short text) but these are still versions of
alphanumeric data types.
Alphanumeric data can store alpha characters (text) or numeric data
(numbers) that will not be used for calculations. In Access this is called a text
field. Note:
A Boolean (or logical) data type stores data as -1/0 but can display it as Yes/ Microsoft Excel is not suitable for database tasks as you cannot define data types.
No (or True/False, -1/0).
A numeric data type (as the name suggests) is used to store numeric values Note:
that may be used for calculations. This does not include numeric data such as
telephone numbers, which should be stored in an alphanumeric data type. In The data types that you select when creating your database will restrict what data
can be stored in your database. If you set a numeric field to be an integer (with 0
Access this is called a number field.
decimal places), then import data with 2 decimal places, like 45.87, Access will
There are different types of numeric field including:
only store 45. The rest of the data will be lost and cannot be recovered. It is
integer sub-type, which stores whole numbers. In Access you can select
important that you chose your data types very carefully.
an integer field or a long integer field. It is wise to use a long integer
field if it is going to contain three or more digits

Designed and Presented by Ajiro Ndi 3

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL

You work for a small garage called ‘Dodgy Dave’s Motors’. This garage sells used
For this question, the descriptions help you to work out meaningful field names. These
cars. Using a suitable database package, import the file [Link]. Assign the
following data types to the fields. should always be short enough to allow printouts to fit easily on to as few pages as

Field name Data type Some field names are possible. The first example is Who manufactured the car?; this could be shortened to
Who manufactured the car? Text inappropriate. Create
Manufacturer or even Make. Make is short, meaningful and appropriate, so use that.
Model Text appropriate and meaningful field
Colour Text names for those fields. You may Price that we bought the car for could be changed to Purchase Price, Purchase, P Price,
Price that we bought the car for Numeric/Currency/2 decimal places add another field as a primary P_Price or just PPrice. Although Access will allow any of these, do not use field names
Price that we will sell the car for Numeric/Currency/2 decimal places key field if your software
requires this. Save the database. with spaces in them, as they may cause problems if you try to do more complex
Year Numeric/Integer

Extras Text operations with the database. You could use any of the other three options, as all would
Does the car need cleaning? Boolean/Logical
be acceptable. For this question, use PPrice. Similarly, the next field can be called SPrice.
Note: Consider the final field, Does the car need cleaning?. Simply using the fieldname Clean
It is important to make sure that you use the field names exactly as given in could give the wrong idea, as it could mean ‘Does the car need cleaning?’ or ‘Is the car
a question, unless you are asked to provide appropriate and meaningful field
names. clean?’. It is sensible to plan this and make the changes in the .csv file before importing

the data into Access. so lets use Valet

In this question you are asked for appropriate and meaningful field names, so start
by looking at the detailed descriptions given instead of the field names, or even
examine the data to work out what information each field contains

Designed and Presented by Ajiro Ndi 4

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
CREATE A FLAT-FILE DATABASE FROM AN EXISTING FILE IMPORT DATA FROM EXISTING FILES

To import the file [Link] (remember, we saved

it with a new filename in an earlier section) for
the task, select the External Data tab. In the
Import & Link section, click on the New Data
Source icon, then from the drop-down menu
select From File. From the sub-menu select the
option for Text File as files saved in .csv format
are text files with each data item separated from
the next by a comma.

Designed and Presented by Ajiro Ndi 5

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
The Import Text Wizard SET APPROPRIATE DATA TYPES
window will open. As comma Check that all the field names and data
separated value (.csv) files are types match those specified in the task. In
delimited files (the comma is this case the PPrice, SPrice and Valet fields
the delimiter), select the do not have correct data types. The PPrice
Delimited radio button and SPrice fields need to be changed to
numeric (currency) fields and the Valet field
needs to be changed to a Boolean (Yes/No)
field.
Ensure the Code Page is set to Western
European Windows and the Date order is in
DMY

For the next part of the wizard, make sure that Comma is selected using the radio buttons
(unless you have changed the .csv file so that it uses semicolons as delimiters). Examine the
first row of the data and decide whether this row contains the field names that you need or if
it contains the first row of data. If the first row contains the field names click on the First Row
Contains Field Names tick box. As you tick this box, the first row changes from 9 to 10

Designed and Presented by Ajiro Ndi 6

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
In the next screen, ensure that the
radio button for Let Access add
primary key is selected – this adds
a new field called ID to the table;
Access will use this as the primary
key field.

in the Import to Table: box, enter tblCars. This is a meaningful table name as
‘tbl’ shows you that it is a table and ‘Cars’ gives relevance to the data. EDIT DATA TYPES
Changes to the data types, or other properties, can be made from the Home tab. In the
Views section click on the Design View icon.

SET THE NUMBER OF DECIMAL PLACES

The task instructed you to set the PPrice field to two decimal places. You can check this
by clicking the left mouse button in the PPrice field and viewing the number of Decimal
Places in the General tab at the bottom of the window.

Note:
The icon in the Views section of the Home tab will let you change between
Datasheet and Design View.

Designed and Presented by Ajiro Ndi 7

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
SET THE DISPLAY FORMAT OF BOOLEAN FIELDS
To change the Boolean field so that it displays Yes or No (note, it does not store the
data like this), click in the Valet field. In the General tab, select the Format cell. Use You work for a shop selling office supplies called ‘Easy as ABC’. Using a suitable
the drop-down list to select the Yes/No option. database package, import the file [Link]. Use these data types for each field.

Note: Field name Data type

Code Numeric/Integer
If you need percentage values, set an integer or long integer data type and select
Type of product to be sold Text
Percentage from the Format drop-down menu for this field.
Description of the product to be sold Text

Quantity of items in each pack Numeric/Integer

Colour Text

Sales price Numeric/Currency/2 decimal places

Purchase price Numeric/Currency/2 decimal places

Discount Boolean/Logical

Some field names are inappropriate. Create appropriate and meaningful field names
for those fields. Use the Code field as your primary key. Save the database.

Designed and Presented by Ajiro Ndi 8

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Move the cursor between two field names
ENTER DATA USING A TABLE
until it looks like this and then double click.
Data is normally entered into a database using a form but, if a form is not asked This will adjust the display widths of the
for, it may be quicker to use the table to enter new data. columns. Scroll down the list of cars until you
reach the entry with a star next to it, which will
allow you to add a New car at the bottom.

Open the database that you saved in Question 1. Add this new car to the database.
Make Model Colour PPrice SPrice Year Extras Valet

Ford Focus Silver 1350 2285 2008 Alarm Central Locking Alloy Wheels Yes
Note:
Once you open the database Do not attempt to enter any other characters, such as the currency symbol. As you
saved in Question 1, First press the Enter key after adding the prices Access will set the data into currency
click on Enable Content format.
button For other data each time you press the Enter key, Access automatically saves the
To open the table in changes you made to the data.
Datasheet View, double
click the left mouse button Note:
on the table name. Check your data entry carefully using visual verification. This is when you compare
the original data on paper (in this case, in Question 2) with the data that you have
To make sure that all the columns are entered into the computer. Data entry errors in a database may cause problems
fully visible, click the left mouse when you try to use the database to search or sort.
button on the grey square to the left
of the ID field name to highlight the
entire datasheet.

Designed and Presented by Ajiro Ndi 9

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
ADD A FIELD TO AN EXISTING TABLE
Data is normally entered into a database using a form but, if a form is not asked
for, it may be quicker to use the table to enter new data.

Open the database that you saved in Question 2. Add a new field to the database
called PDate. Add the purchase date of 20 December 2020 for the last car added to
the database. Save the database as Question 3.
Open the database
and open the table
tblCars in Design View. You may need to double click to the right of the PDate column to widen the column.
Move to the empty
row below the Valet
field and enter the
Field Name PDate. In Open the file saved in Task 1. Add these three items of stationery to the database.
the Data Type box use
the drop-down list to Code Type Description Quantity Colour SPrice PPrice Discount

select the Date/Time 44282 Lever Arch File Laminated Lever Arch Files 1 Red 57.22 28.96 No

type. 44283 Lever Arch File Laminated Lever Arch Files 1 Yellow 57.22 28.96 No

47478 Spine Label Eastlight Spine Labels 100 30 13.86 Yes

Choose the most appropriate Format for the task. In this case, the task asks for a Long
Date format. Save the database as Question 3 and select the Datasheet View. Move
the cursor into the PDate field for the new record (the Silver Ford Focus) and use the
Calendar icon to select the correct date.

Designed and Presented by Ajiro Ndi 10

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Select the Database Tools tab, locate the Relationships section, then click
CREATE A RELATIONAL DATABASE
on the Relationships icon.

You work for Ajiro Tech British School. You will edit some data about the
Next
Mathematics Faculty. Using a suitable database package, import the file
[Link]. Use these field names and data types:

Field name Data type Set the SCode field as a key field. Import the
file [Link] as a new table in your
SCode Text
database. Set the Student_ID field as a key
FName Text
field. Create a one-to-many relationship as a This window shows the link between the fields in
SName Text
link between the SCode field in the Teachers’ both of the tables. The bottom of the window
Subject Text table and the Maths field in the Students’ displays the type of relationship that you have
Room Numeric/Integer table. created.
A screenshot of this window taken with <prt
Use the techniques you practised in Question 1 to import the two tables into the sc> key or snipping tool will copy this into the
database so that each table looks like this. clipboard. Paste this into the document that
you will present as evidence of your method.

When you click <create> the

window will disappear and the
Relationships tab will look like
this.

Designed and Presented by Ajiro Ndi 11

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
In this relationship SCode is the primary CREATE A DATA ENTRY FORM
key field in tblTeachers and Maths is the
foreign key field in tblStudents. If you wish
to view or edit the relationship again, you
can double click the mouse on the
relationship line that joins the two tables. Open the file saved in Question 4. Add new data entry forms to collect data for all
fields in both of the tables.

Open the file saved in Question 4. The best way to create a data entry form is to select
the Create tab, then in the Forms section, click on the Form Wizard.

Open the file saved in Task 2 Import Assign a new field as a key field. The Form Wizard window opens.
Select the table that holds the fields
the file [Link]. Use these field Import the file [Link]. Use
that you will include in the form. If
names and data types: these field names and data types: the form needs fields from more than
one table, then select a query (you
Field name Data type Field name Data type
You can move each field across will use these later in the chapter).
Order_No Numeric/Integer Customer_ID Text into the form using the single We will create the first form by
arrow key, but, as you want all
Customer_No Text Name Text the data from this table on the
selecting tblStudents, so leave that
form, use the double arrow key. selected in the top selection box.
Product_Code Numeric/Integer Address_1 Text

Units_Sold Numeric/Integer Address_2 Text

Address_3 Text
Set the Customer_ID field as a key field. Create a Go to the Current Next Last New

one-to-many relationship between the Code field Zip_Code Text first record record record record record

in the Stationery table and the Product_Code field Discount_% Numeric/2 decimal places
in the Orders table. Create a one-to-many
relationship between the Customer_ID field in the Take screenshot evidence showing the:
Customers table and the Customer_No field in the field names and data types used in these
Orders table. two tables
relationships between the three tables.

Designed and Presented by Ajiro Ndi 12

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Repeat this process for tblTeachers to create the second data entry form, which Open the database saved in Question 5. Double click the left mouse button on
should look like this. frmStudents, which is in the list of database objects under forms, on the left.
The data entry form may need editing to make it
Select the Home tab, then the drop-down arrow in the Views section to get the drop-
easier for a different target audience to use. Using down menu for the different ways of viewing the form. Select Design View.
short and meaningful field names to store the
data may not be easy for other users to Click on the Shutter Bar Open/Close Button to hide the
understand, particularly if they do not work with ‘Navigation pane’, giving you more room to work.
The tab will change automatically to Design. From the
databases regularly. For example, if children were
Tools section of this tab, select the Property Sheet icon.
to add their data, simple questions would be
This will open the Property Sheet pane for the current
better than encoded field names, along with object. Move the cursor into the Form Header and click on
instructions on how to complete the form. the outline of the text box so that it changes to orange.

EDIT A DATA ENTRY FORM

Highlight the text in this label box and
change it to a more suitable heading. If
the text does not fit in the label box,
grab the drag handles and make it
Open the file saved in Question 5. Edit the data entry form for student data to make larger. You may not be able to see the
it easier for students to enter their own data. lower drag handles; if you need to use
them, drag the Detail bar down slightly,
but remember to move it back up again
later.

Designed and Presented by Ajiro Ndi 13

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
If you wish to change the font, colours or ADD A NEW FIELD TO AN EXISTING FORM
formatting of the text (or any other form Open the file saved in Question 6. Two new fields are to be
element) this can be done in the Property added to the students’ data. Use these field names, data types
and field descriptions:
Sheet. In this example, Text Align has been
Field name Data type Description
changed to Center. The Fore Color and
Year_Group Numeric/Integer The school year between 7 and 11 inclusive
Back Color have also been changed by
Tutor Text The name of the student’s tutor
clicking on them, then by choosing colours
Open the database. Remember you hid the Navigation Pane. Restore it using the Shutter Bar Open/Close Button
from the palette.
arrow.
Open tblStudents in Design View. In the blank row below the ‘Science’ field, enter the Field Name, Year_Group
In the Detail row each field has two boxes. (you cannot shorten it to Year as this is a reserved word in Access – try it and see …). The Data Type always starts
The left one is the label box. This is what is in Short Text format; click on that cell and use the drop-down menu to select Number.
displayed to the user. The right box is a
Type the field description into the Description box (copy the text
text box and this is joined to the data from the table in the question). This helps to document the
table. This box is where, when the form is database. In the blank row below this, add the new Field Name,
displayed in Form View, the user will enter Tutor. Leave the Data Type as Short Text. Type the Description,
the data. Select each label in turn and edit again using the text from the task. To help improve the form and to
the text so it is more meaningful for the save lots of storage space, we know that the tutors’ initials are three
students. You may need to resize some of characters in length. In the General tab at the bottom, set the Field
the label boxes so that all the text fits. Each Size to 3.
box has a large drag handle in the top left
corner that will allow you to drag the box Open frmStudents in Design View. Pick up the top of the
Form Footer and drag it down to give enough room to add
around the form to rearrange the form
the two new fields. The Year_Group field could appear on
without resizing the box. This is useful if the form as a text box but, as this data can only hold five
you are creating your form to a particular possible values (because Ajiro Tech British School only has
design. From the Views section of the years 7, 8, 9, 10 and 11) it would be a suitable field for radio
toolbar, use the drop-down arrow under buttons (Access calls these Option Buttons) within an option
the View icon to select Form View like this. group. From the Form Design tab, in the Controls section,
select the Option Group icon. You may need to use the
drop-down menu to find it.

Designed and Presented by Ajiro Ndi 14

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
When you have changed all of the values click .
EDIT A DATA ENTRY FORM In the next window, change the radio button
Use radio buttons from the top option to Store the value in this
field:. Using the drop-down menu to the right,
Drag the frame for the Option Group
select the Year_Group field.
into the Detail section of the form,
clicking the left mouse button once;
this action will open the Option Group
Wizard window. Enter six Label
This will make sure that, when a radio button is selected, the value for that radio button
Names, one item on each row – in this
case Year 7, Year 8, Year 9, Year 10, is stored in the correct field, Year_Group, within the table. Click <Next> to choose the
Year 11 and an additional option for style of options selected (radio buttons (which the software calls option buttons),
‘No year group selected’ – before tick/check boxes, or toggle buttons) and to choose how the Option box will appear on
clicking on <Next> the form. Make your choices, then click <Next> . Give the frame a meaningful caption
such as Which year group are you in? before selecting <Finish>.
In the next window choose the top radio button
The option block will look similar to this
option for Yes, the default choice is: and select
in Design View. To add the Tutor field
the option you typed in for No year group
to the form, select the Text Box icon
selected.
from the toolbar.
Drag the text box for the Tutor field on
to the form; you do not need much
Click <Next>. Each label has a value assigned to space as this field only needs to hold
it. Access has tried to assign values for you, but three characters.
you need to change all the settings in this
example. For Year 7 set the value to 7, Year 8
set to 8, and so on. For No year group selected Click the cursor on the Unbound text box and, within the Property Sheet for this
set the value to 0. control, select the Data tab. In the Control Source box, use the drop-down menu to
select the Tutor field.

Designed and Presented by Ajiro Ndi 15

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
WHAT DOES A WELL-DESIGNED FORM LOOK LIKE?
Resize the label box on the left and
add the text Tutor as the label. The most important feature of form design is to keep the form
simple, with clear questions, using closed questions where
possible. This will limit the different answers to be stored in
the database and will make it easier to search the database. A
well-designed form has similar fields grouped, but not
crowded, together, with white space between each data
entry box.
The form has a title that states what data is being
collected.
Each field has appropriate space for the data that will be added,
The text box changes from unbound to show the Tutor and there is space between each field. The form has been
field. The text boxes for English, Maths and Science are appropriately filled by the text boxes but there is enough white
space to keep it from being overcrowded.
also too large as they can only contain three-lettered
staff codes, so reduce these three field sizes together by
selecting all three text boxes together and dragging the Radio buttons (or drop-down menus) are used where possible. There are navigation
right edge in so that they match the Tutor field. Save the buttons on the form (already added by Access) to allow a user to add new records and
database as Question 6. The finished Design view and move between records.
Form view look like this.
In Design View of the form, move and resize the controls so that your form looks like
the diagram above. Change the text to be right aligned text in each of the label boxes.
Re-save the database.
Use drop-down menus
It is often easier for the user of a database to use a drop-down menu to select data
rather than typing each data item every time. These can only be used when there are a
limited number of possible entries for a field, so fields containing data such as names
could never use drop-down menus – unless they contained every name in the world,
which would not be practical.

Designed and Presented by Ajiro Ndi 16

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
In the next window select Table: tblTeachers
then click on <Enter>.

Open the file saved in Question 7 Change the Tutor field so that the user can select
their tutor from the list of staff codes in tblTeachers.
Open the file saved in Question 7 and open frmStudents
in Design View. Delete the controls for the Tutor field
(both the label and text box). You will replace the text
box with another type of control. From the Form Design Combo List
box box
tab, in the Controls section there are two suitable
options, a combo box or a list box. In the next window select Table:
tblTeachers then click on <Enter>.
If you wish to allow the user to type new items into the drop-down list then a
combo box would be suitable, but for this task, where you do not want the user to
be able to add to the list items, select a List box.
Drag the control onto the form. The List
Box Wizard window opens. Select the
radio button for I want the list box to get Double click the left mouse button on the
the values from another table or query, SCode field to move it from the ‘Available
as the data is held in the teachers table. fields:’ to the ‘Selected fields:’, then click
Click on <Enter>. <Enter>.

Designed and Presented by Ajiro Ndi 17

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Select the SCode field in box 1 and make Click on <Next>, then change the label for
sure that the ‘Ascending’ button is visible, the list box name from the suggested name
to show the list in alphabetical order. Click to Tutor, then click on <Finish>.
<Enter>. Resize/move the controls and change the
alignment of the text in the label to right
aligned so that it matches the other labels.
Save the database as Question 8.

Adjust the SCode column width

using the drag handle, then click
<Enter> .

The value that you select will

need to be stored in the Tutor
field so select from the Store
that value in this field: drop
down menu the option for
Tutor.

Designed and Presented by Ajiro Ndi 18

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Note:
MANIPULATE DATA If you select a previous query rather than the table, you are likely to get incorrect results.

PERFORM SEARCHES For this question it would be appropriate to show the customer all the fields except the ID
field, the price that the garage bought the car for (the PPrice field) and the date the garage
You can search for data in Access using a query. This allows you to select a subset
purchased the car (the PDate field).
of the data stored in your table. Each query is created, saved and can be used
again later. If new data is added to the table, when you open a query again it will Move all of the fields into the query using the
select the subset from all the data, including the new data. double arrow key.
Select the ID field, the PPrice field and the
PDate field in turn and click on the single
arrow to remove them from the selection.
Open the file that you saved at the end of Question 3. A customer would like a car You need to enter a name for the query. This query may
made by Ford. Find the customer a list of all the cars in the garage made by Ford. be turned into a report at some point and the name you
give the query may become the title for the report.
Open the database that you saved at the end of
Question 3. You do not need to open the table that
Select the radio button for Modify the query
you created earlier. Select the Create tab and find the
Queries section. Click on the Query Wizard icon. design, then click on <Finish>.

This opens the query in Design View. Datasheet

This is the easiest way of performing a search and opens View can be seen at any time by selecting the
the New Query window. Select the Simple Query Wizard
drop-down list under the View icon. However,
and click on OK . In the Simple Query Wizard window,
at the moment the query will still contain all of
make sure that the correct table name has been selected
in the Tables/Queries box. As this is your first query this the records as you have not yet performed the
is the only option in this box, but each time you create a search, so make sure you are in Design View .
new query it will be shown here.

Designed and Presented by Ajiro Ndi 19

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
To perform the query, move the Create a query in a similar way to the one for Question 9. Make sure you have
cursor into the Criteria: row of tblCars selected and not your Ford query. Select all the fields and, when in Design
the Make field and type in Ford. View, enter Ford in the Criteria: row of the Make column and Yes in the same
Criteria: row for the Valet column. The selection will look like this and only two
Note: cars will be found using this search.
You do not need to use speech
marks as Access will put these in for
you. This will extract only the cars
made by Ford.

Now select the Datasheet View to Note:

see the results of the query. The
This is called an AND Query, because the Make has to be Ford AND the Valet field has to be Yes.
number of records can be seen at
the bottom of the window in this
view. There should be 25 Ford cars
in the query.

Open the file that you saved at the end of Question 10. The manager would like to see
all the details of all the cars made by Ford or Vauxhall.
Open the file that you saved at the end of Question 9. The manager would like to
see all the details of all the Fords that need valeting. Create a query in a similar way to the one for Question 10. In the Simple Query Wizard window,
make sure that the correct table name has been selected in the Tables/Queries box. If you select
one of the previous queries rather than the table, you are likely to get incorrect results.

Designed and Presented by Ajiro Ndi 20

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Select all the fields and, when in Design View,
enter Ford or Vauxhall in the Criteria: row of
the Make column. The selection will look like
this. Open the file that you saved at the end of Question 12. The manager would like to see
all the details of all the cars that have alloy wheels.
37 cars will be found using this search.
By examining the data in the database, you can see that the text ‘Alloy Wheels’ could
Note: appear in the Extras field. It may not be the only extra that a car has – there could be
other extras listed before it or after it within the field.
Another way of doing this is to type Ford in
the Criteria: row and Vauxhall in the or: row. To find all the cars with this extra you must create a
query in a similar way as for Question 11. Select all
the selected fields and, when in Design View, in the
Criteria: row of the Extras column, enter the text
*Alloy Wheels*. The stars tell Access that you are
performing a wildcard search. This is a search which
looks for the words ‘Alloy Wheels’ (including the
Open the file that you saved at the end of Question 11. The sales manager would space) anywhere in the Extras fields’ contents.
like to see details of all the cars in stock not made by Ford.
35 cars will be found using this search.
Create a query in a similar way to the one for
Question 11. Select all the fields and, when in Design Note:
View, enter Not Ford in the Criteria: row of the Make
To search for something that is at the start of the data, use Text*; for example, Bl* in the Colour
column. The selection will look like this.
field will find all the cars with the first colour Blue or Black, but would not find colours such as
43 cars will be found using this search. Light Blue. Placing the star at the start of a search string will only find those things ending with
the search string.
Note:
Another way of doing this is to type <> Ford in the Criteria: row

Designed and Presented by Ajiro Ndi 21

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Open the database that you saved at the end of Question 8. Create a new query using
the Query Wizard. This is the easiest way of performing a search and opens the New
Query window. Select the Simple Query Wizard then, in the Tables/Queries box, select
Open the file that you saved at the end of Question 13. The sales manager would the table tblTeachers. Move across to the right to the FName, SName and Room fields.
like to see details of all the cars in stock for sale for less than or equal to £4125. Move back into the Tables/ Queries box and select the table tblStudents. Select the
Student_Fname, Student_SName and Maths fields by moving them from Available
Create a query in a similar way as for Question Fields: to Selected Fields:
13. Be careful not to use symbols such as < or £
in the query name. Select all the selected
fields and, when in Design View, enter <=4125
in the Criteria: row of the SPrice column.

19 cars will be found using this search.

Note:
Similar mathematical formulae can be used, with < for less than, > for greater
than, >= for greater than or equal to, and = for equals. These mathematical
formulae cannot be used for queries involving text fields but can be used for
any numeric, date or time fields.

Open the file that you saved at the end of Question 8. Find Mr Varela a list of all
the students that he teaches for Maths; include in this extract his full name and
Click on <Next>. Continue through the wizard until you get to the query in Design View.
teaching room.

Designed and Presented by Ajiro Ndi 22

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL

Open the file saved in Task 3.

Search the database to find the following information for your manager. For each search,
show how you performed the search and the results of the search; the results must show all
the fields in the stationery table.
Find all of the blue stationery items.
Find all of the blue or black stationery items.
Find all of the items where the colour is not blue.
Move the cursor into the Find all of the red items where the discount is ‘yes’.
Criteria: row of the Maths field Find all of the items where the type contains the word ‘file’.
Find all of the items where the description contains the word ‘file’.
and type in AVA. Open the
Find all of the items with a quantity of less than or equal to 10.
query in Datasheet View to Find all of the items where the quantity is 10.
check that you have done the Find all of the items with a quantity of greater than 1.
query as specified. Find all of the items with a quantity of greater than or equal to 10.
Find all of the items where the sale price is less than £10.

The Maths field does not need to be PERFORM CALCULATIONS

shown; to hide it (do not delete it or the
selection of the data will also be lost) Use formulae in queries
move back into Design View. Move the You are sometimes asked to perform calculations at run time. This could be done
cursor into the Show: row of the Maths
in one of two ways. The first method is by creating a calculated field, so that each
field and remove the tick from the check
box. record has a calculation performed on it and the results are stored in a query.
The other method is to calculate on all (or a selection of) the records, for
This field is present in the query but will
not be shown. This query should return example to add (sum) the data from a number of records.
31 records.

Designed and Presented by Ajiro Ndi 23

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Move the cursor into the Field row for the first blank field. Enter the name Profit that
you wish to give this calculated field followed by a colon. The colon tells Access that
the next section is a calculation. Within the calculation, you must place square
brackets around each field name so that Access looks up the data from the relevant
Open the file saved in Question 14.
field.
Produce a new extract from all the data that:
contains a new field called Profit which is calculated at run time – this field will For this question, you need to
subtract the purchase price from the sale price subtract the purchase price from the
contains a new field called Percent to calculate the percentage profit for each sale price. The finished calculation
car at run time. This field will divide the profit by the sale price. will appear like this.

Note:
To create a field that is calculated at run time, you must first open a query. For this
question the query will not be used to search for data but to perform the You can use the builder to achieve
calculation. Select the Create tab, then click in the Queries section on the Query this
Wizard icon; select the Simple Query Wizard.

Then click on <Next>. In the Tables/ Queries box select the table tblCars as the Note:
source of the data. Select all fields using the double arrow key. Click on <Next> Use + for addition, - for subtraction,
twice, name the query Profit calculation, select the radio button for Modify the * for multiply and / for divide.
query design, then click on <Finish>.
In the Views section, select the
In the Design View of the query, Datasheet View. Calculate the
use the bottom scroll bar to scroll profit for three or four cars by
to the right and find the first blank hand or using a calculator and
field compare with the results in the
query to check that you have
entered the formula correctly.

Designed and Presented by Ajiro Ndi 24

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
The question requires you to create a second calculated field, so it would be
sensible to include the new field now and complete the formatting later. To create a
new field called Percent, follow the same procedure, this time adding a formula to
divide the profit by the purchase price. Open the file saved in Task 4. Produce a new extract from all the data in the stationery table that:
contains a new field called Profit which is calculated at run time – this field will subtract the
purchase price from the sale price
contains a new field called Percent to calculate the percentage profit for each car at run time –
this field will divide the profit by the sale price
contains a new field called UnitProfit – this field will divide the profit by the quantity.
For each calculated field, show how you performed the calculation and the results of the
calculation.

Again, check the calculations with a calculator to ensure that you have not made
an error when entering the formula. When you have calculated this field and view
the query in Datasheet View, you may see the values displayed as #####. This
means the column is too narrow to see all the data. Drag the right column handle Open the file saved in Question 16.
to the right so that the data is fully visible. Save the database and close the query. Select only the cars made by Audi, BMW or Mercedes. Produce a new extract from all
the data which, for the each of these makes of car, calculates:
the sum of the sale price
the average sale price
the number of cars in stock.
Sort this data into descending order of average sale price.

This question requires the use of summary data. Open the file and, from the Create
tab, select the Query Wizard, then the Simple Query Wizard followed by <Next>. In
the Tables/Queries box select tblCars and from this table select only the Make and
SPrice fields before clicking on <Next>

Designed and Presented by Ajiro Ndi 25

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL

Click the radio button for Summary This opens the Summary Options window.
data, then select the Summary To calculate the sum of the sale prices, tick
the check box for Sum. For the average
Options… button.
sale price, tick the check box for Avg. For
the number of cars in stock tick the check
box for Count records in tblCars.

Click on the <ok> button followed by SORT DATA IN QUERIES

<Next>. Add a new title for the query,
This data is sorted into ascending order of
such as your name followed by Summary
Make, but the Question required you to
data for Audi or BMW or Mercedes
sort this data into descending order of
query. Select the radio button for Modify
average sale price. Select the Home tab,
the query design, then click on <Finish>
then click on the column heading for Avg
to enter Design View. Enter in the
of SPrice.
Criteria: row of the Make column the
text Audi or BMW or Mercedes. In the Sort & Filter section, select the
descending sort icon

Designed and Presented by Ajiro Ndi 26

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL

PRESENT DATA
Open the file saved in Task 5. PRODUCE A REPORT
Select all types of items, except for any binder. Produce a new extract from all of the
stationery data which, for each type of item, calculates: The word ‘report’ can be quite confusing. A dictionary definition is ‘a document that
the average purchase price gives information about an investigation or a piece of research’. For our purposes, a
the average sale price report has this generic meaning: ‘a document that gives information’. This is often
the number of items in stock. confused with a report created in Access. The report created in Access will often be
Sort this data into descending order of average purchase price. the most suitable report for questions and tasks, but sometimes it may be better to
Show how you performed the summary query and the results of the calculations. produce a report in a word processor, copying and pasting information into a
document. For each task you will need to decide which method is the most suitable.

Open the file saved in Question 17.

Produce a report that:
shows all of the cars made by Ford
displays only the Make, Model, Colour, SPrice, Extras and Valet fields within the
width of a landscape page
has the text ‘Report by’ and your name on the left in the header of each page
has a title ‘All Ford cars in stock’ centre aligned at the top of the first page
has a subtitle ‘request for Mr David Watson’ right aligned, in red, at the top of the
first page.

Designed and Presented by Ajiro Ndi 27

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Click on <Next>. Grouping is not needed at
Open the database saved in Question this level, so click on <Next> again. You have
17. Select the Create tab and find the not been asked to sort the report for this
Reports section. Click on the Report question (this is covered later in the slide), so
Wizard icon to open the Report Wizard click on <Next> again to get this Report
Wizard window.
window.
Use the Layout section to choose how the
page will be laid out; in this case a Tabular
format has been selected.
The question asked you to select a single
Note: landscape page. The page orientation is
chosen using the Orientation radio buttons.
You must create the query first and then base the report on the query. Select Landscape then click on

In the Tables/Queries box you need to

select the correct query. For this question
the report will be based on the query to
select only the Fords (you created this
query in Question 9). Use the arrow
buttons to move the fields required by the
question from Available Fields: into the
Selected Fields:

Note: Change the report name so that it reads ‘All Ford cars in stock’ (which is the title from
If a question asks for specified fields (as this task asks for ‘displays only the Make, the task). As you still need to add the subtitle and ensure that the layout is correct,
Model, Colour, SPrice, Extras and Valet fields’) make sure you do not add extra select the Modify the report’s design radio button and click on <Next>. The Design
fields. Make sure that you also place the fields in the order specified in the View of the report will look similar to this.
question.

Designed and Presented by Ajiro Ndi 28

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Each section of the report is shown with a light grey bar. The top section is the Report
Header. Anything that you place in this section appears only once at the start of the
document. Anything that you place in the Page Header is shown at the top of each
page, in this case the field names. Similarly, information in the Page Footer is shown at
the bottom of each page. The Report Footer appears at the very end of a report,
If you had selected different layout options although in this example the Report Footer is empty (it is not shown in white) and
such as justified, or columnar, the first page therefore will not be shown in this document. The Detail section is the most important,
of each report would look similar to this. as this single row is where the data is shown for each car. This single row will appear as
many rows (as many as there are Ford cars in the database) and display the details of
justified columnar each record.

Open the database that you saved in Task 6.

Produce a report that:
displays all the data in the stationery table within the width of a landscape page
has your name on the right in the header of each page
has a title ‘All stationery in stock’ centre aligned at the top of the first page
has a subtitle ‘request for the manager’ right aligned at the top of the first page.

Designed and Presented by Ajiro Ndi 29

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
PRINT PREVIEW:

Open the database that you saved at the end of Question 18.
Produce a report that:
displays all the data for the Make, Model, Colour, SPrice, Year and Extras fields
for all the cars with alloy wheels from Question 12, within the width of a portrait
page
has your name in the report header followed by ‘Cars with alloy wheels’.

BREAKDOWN
Open the file Question 18. Select the Create tab and, in the Reports section, click on the
Report Wizard icon. In the Report Wizard window, in the Tables/ Queries box, select the
query for alloy wheels (that you created in Question 12). As the question says ‘display all
the data’, and specifies the fields, use the arrow buttons to move only these fields from
Available Fields: to the Selected Fields: box. Go through the wizard as you did for the
previous question, making sure that you set the page Orientation to Portrait.

You must show all of the required data in full Open the database that you saved in Task 7.
Produce a report that:
Change to Report View and check that all the data and labels fit within the width of a single
displays the data for all the items where the quantity is greater than or equal to
page. Other than the Extras field (which holds the most data), We can enlarge the Extras field
10, selected in Task 4, within the width of a portrait page
by making the control for that field twice as deep.
Change to Design View and click the cursor on the top edge of the Page Footer. Drag has your name in the header of each page
this down about 8mm. In the Detail row, click the cursor on the lower edge of the has a title ‘Quantity > = 10’ centre aligned at the top of the first page.
Extras control. Drag this down to double the height of this control.
Change to Report View and check that all the data and labels fit within the width of a single page

Designed and Presented by Ajiro Ndi 30

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
EXPORT DATA Note:
If you need to export the data into .csv format (comma separated values), export it first into Excel,
Sometimes whole reports, queries or the data within them need to be exported into
then save it in .csv format from Excel.
other packages to be manipulated as part of a report for someone, or to create a
graph or chart.

Export the report saved in Task 8 into:

Export the report saved in Question 19 into rich text format so it can be included in rich text format
a word-processed document. a format that can be used to produce a graph
In the Navigation pane (on the left side of the comma separated value format.
window), find the report that you saved in
Question 19 and right click the mouse button on
the report name to get the drop-down menu.
HIDE DATA IN A REPORT
Select the option to Export. This will open another
There are times when information in a report needs to be hidden in some way. In real
drop-down menu. You need to export into .rtf
format, so select Word RTF File. This opens the
applications a single report would be created for more than one task and some data
Export – RTF File window. Click on the <Browse> would be hidden. This process is often done automatically using a created report and a
button to select a folder to save the document into. programming language. Although that is beyond the scope of this book, the ability to
You will need to use this file for another question,
hide fields within a report is useful. An example of this is when an invoice is produced
so select the tick box for Open the destination file
after the export operation is complete, then click
for a customer and the same document is used as a delivery note, so that it shows the
on <OK> . The exported file will appear in Microsoft details of the items ordered but the costs are hidden. In Access this can be done in one
Word. Close the Export – RTF File window. of two ways: the first is to make a control invisible; the second is to use a background
Note: colour that matches the text colour.

If you need to export a report without any formatting, select the Text File option

Designed and Presented by Ajiro Ndi 31

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL

Create a new report showing all the data for all the cars in stock made by Audi,
BMW or Mercedes. To set a black background for the
Hide all the labels and data for the ID, Valet and PDate fields from the report. Hide PPrice data, in the Detail row of the
the PPrice data (but not the title) by setting a black background. report select the control for PPrice.
Move the cursor into the Property
Using the methods used so far in this chapter, create a new query from tblCars to Sheet, selecting the Format tab. Find
select the three makes of car. Create a new report set in landscape format to display the Back and Fore Color section and
use the <insert> icon to select the
all fields for these cars, like this.
colour palette.

Select the black colour rather than the white background. Set the Fore Color to black in
the same way. Change from Design View into Report View to see the changes.

Go into the Design View of the report; holding down the <ctrl> key select all the
controls for the ID, Valet and PDate fields. Open the Property Sheet and, in the
Visible section, change the setting from Yes to No, which will hide these controls.

Save the changes to the report and close it.

Designed and Presented by Ajiro Ndi 32

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Note:
Open the file you saved in Task 8. Move to All Access Objects and Reports.
Use copy and paste to make a copy of the report for the quantity is greater than or
equal to 10, before starting Task 10. Open the file that you saved at the end of Question 20.
Find all the cars with a sale price of less than £4000 and, for these cars, produce labels
that:
have a page orientation of portrait
fit two side by side on the page
have a 16-point, centre aligned heading ‘Special Offer’ at the top of each label
Open the report created in Task 8. show only the fields Make, Model, Colour, SPrice, Year and Extras, sorted into make
Hide the label and data for the Discount field in the report and hide only the data in and model order
the PPrice field by setting a black background. have your name at the bottom right of each label.
Design a new query to extract only the cars with a sale price of less than £4000,
Note: selecting only the Make, Model, Colour, SPrice, Year and Extras fields from the table
If you wish to change the display formats of any field, this can also be done in the Property
as you step through the Simple Query Wizard. When you have selected these cars,
Sheet pane using the Format tab. However, it is better to set the formatting for the fields in close the query and click the left mouse button on the query so that it is highlighted
the Design View of the table as changing the display properties will not change the way that like this.
the data is stored, and this could lead to errors if fields are used for calculations. From the Create tab, find the
Reports section and click on the
Labels icon.
PRODUCE LABELS
You may be required to produce other forms of output from your database, for
example, producing labels to advertise a product or address labels for mailing letters
to customers.

Designed and Presented by Ajiro Ndi 33

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
This opens the Label Wizard. Select any In Available fields: double click on
label format that contains two labels the Make field. Press <Enter> to
across the page; in this case, use the move to the next line. Add each field
Avery J8166 labels as they are slightly in the same way, entering the new
larger than some of the other labels (and line then the field. When all of the
it is therefore easier to fit all the data fields have been moved across, add
and labels on to each label). Click on a final row with your name, then
<Next>. click on <Next>.

The next screen asks for the font size and

colour of the text on the label. Leave this
set to a small size (it is easier to enlarge Move the Make, then Model fields
this later, if needed, rather than to reduce across into the right to sort the
it), such as 8 points high. Click on <Next>. labels by make and model as
specified in the question, then click
on <Next>.

Type the text ‘Special Offer’ in the grey

area as the top row of the label. Press to Give the labels an appropriate name and
move down to the second row. select the radio button for Modify the
label design. Click on <Finish>.

Designed and Presented by Ajiro Ndi 34

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
The Design View looks like this. Select The six middle controls (those containing
all the controls except the one the fields) need labels. Click on the Design
containing the text ‘Special Offer’. Drag tab and select the label box. Drag the label
these down about 8mm. Move the box out to the left of the Make field, enter
lower control down the label about the text ‘Make’ and, in the Property
8mm. Select the middle six controls Sheet, set the Font Size to 8. Set the
and move the left edge to the right Height of this control to the same as the
about 25mm. Make field control

Select the control containing the text

‘Special Offer’ and stretch it down to Copy this control, paste it five times and
give it more space. Open the Property move the new labels to the left of each
Sheet and set the Font Size to 16 and field. Edit the text so that each label box
the Text Align to Center. Stretch the shows what the field is.
Extras field down to give it more space,
so that all the data should be visible.
This will need to be checked when the
labels are produced and edited again if
necessary. Your name at the bottom of
the label should also be right aligned
by setting the Text Align to Right. Check the labels’ layout from the Home
Check the labels’ layout from the tab using Print Preview to see all the
Home tab, using Print Preview to see labels set out on the sheet.
all the labels set out on the sheet or If need be, make any adjustments to the
Report View to see a single label. Save controls.
the labels

Designed and Presented by Ajiro Ndi 35

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Open the file saved in Question 22. Create a new report from all the data in the extract
using the Report Wizard. In the Tables/Queries box select the profit calculation query as
the source of the data. Select all fields using the double arrow key. Click on <Next> three
Open the database that you saved at the end of Task 9. Find all the stationery times. Set the page Orientation to Landscape then click on <Next>. Use Profit
items where the discount is ‘Yes’ and the sale price more than £30. calculation as the report title. Select the radio button for Modify the report’s design,
then click on <Finish>. Adjust all the field widths so that all the data fits on the page.
For these items produce labels that:
Move to the Detail row of the report. Hold down the key and select the PPrice, SPrice and
have a page orientation of portrait and fit two side by side on the page
Profit field controls. In the Property Sheet select the Format tab and use the drop-down
have a 20-point, right-aligned heading ‘Discount Offers’ at the top of each
menu in the Format section to select Euro.
label
show only the fields Type, Description, Colour and SPrice, sorted into colour Move to the Decimal Places
order section and select 2. Click on the
have your name centre aligned at the bottom of each label. View icon to go into Report View
and check that the formatting for
these fields is in Euro and contains
FORMAT REPORTS two decimal places.

Repeat this process for the Percent field. Click the left mouse button on the control for Percent in
the Detail row of the report. Select the Format tab and use the dropdown menu in the Format
section to select Percent from the drop-down list. Move to the Decimal Places section and select 0.
Open the file saved in Question 22. Using the extract that contains the Go into Report View to check that the formatting for this field is now correct and contains no
calculated fields Profit and Percent, produce a new report from all the data that: decimal places. This is fine, but some field widths may need readjusting so that all data and labels
has the PPrice, SPrice and Profit fields formatted as Euro with two decimal are fully visible. Adjust these before saving the completed report.
places
Note:
has the Percent field formatted as a percentage value with no decimal
places. If a question asks for a currency not held in this drop-down menu, select your local Currency format

Designed and Presented by Ajiro Ndi 36

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
You can use the Profit calculation report
Open this report in Design View.
Click the left mouse button on the bottom edge of the Report Footer and drag this
down about 2cm, so that this footer is now visible.
Open the file saved in Task 11.
Select the Design tab, move to the Controls section and select the Text Box icon.
Using the extract that contains the calculated fields Profit, Percent and UnitProfit,
produce a new report. Apply appropriate formatting to this report. All currency Click the left mouse button on the bottom
values must be in Euros with two decimal places. All percentage values must be set edge of the Report Footer and drag this
to one decimal place. down about 2cm, so that this footer is
now visible. Select the Report Design tab,
FORMULAE IN REPORTS move to the Controls section and select
Other calculations may be needed on the data selected. These include calculating the Text Box icon
the sum (total), average, maximum or minimum values of selected data, or Move down into the Report Footer, click the left mouse button and drag to place a
counting the number of items present in the selected data. All of these functions new control, in this case a text box, directly below the Profit column.
can be produced within a report in Access. This positioning is important as this control will be used to calculate the total profit
for the data in this report.

Open the file saved in Question 23. Produce a new report from all the data that:
displays at the bottom of the report the total profit if all the cars were sold
displays at the bottom of the report the maximum, minimum and average
profit values
displays the number of cars in this report.

Designed and Presented by Ajiro Ndi 37

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
You can use the Profit calculation report
Open this report in Design View.
Click the left mouse button on the bottom edge of the Report Footer and drag this
down about 2cm, so that this footer is now visible.
Open the file saved in Task 11.
Select the Design tab, move to the Controls section and select the Text Box icon.
Using the extract that contains the calculated fields Profit, Percent and UnitProfit,
produce a new report. Apply appropriate formatting to this report. All currency Click the left mouse button on the bottom
values must be in Euros with two decimal places. All percentage values must be set edge of the Report Footer and drag this
to one decimal place. down about 2cm, so that this footer is
now visible. Select the Report Design tab,
FORMULAE IN REPORTS move to the Controls section and select
Other calculations may be needed on the data selected. These include calculating the Text Box icon
the sum (total), average, maximum or minimum values of selected data, or Move down into the Report Footer, click the left mouse button and drag to place a
counting the number of items present in the selected data. All of these functions new control, in this case a text box, directly below the Profit column.
can be produced within a report in Access. This positioning is important as this control will be used to calculate the total profit
for the data in this report.

Open the file saved in Question 23. Produce a new report from all the data that:
displays at the bottom of the report the total profit if all the cars were sold
displays at the bottom of the report the maximum, minimum and average
profit values
displays the number of cars in this report.

Designed and Presented by Ajiro Ndi 38

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
If the Properties pane is not showing, Change to Report View and make sure that the control is in the correct place and
right click the mouse button on the appears to give the right answer (it is not too large or too small).
text box that you have just created
Rather than repeating this process four more
then select Properties from the drop-
times, it will be quicker to copy and paste these
down menu. In the Property Sheet
controls and edit each one to give the required
select the All tab, find the Control
results.
Source section and type the formula
=SUM([Profit]) into this row. The In the last four controls containing labels, change the
Property Sheet will change to this. Captions to ‘Maximum profit’, ‘Minimum profit’,
‘Average profit’ and ‘Number of cars’. Select the
The round brackets are part of the SUM second Text Box (for the maximum profit) and change
function; the square brackets tell Access the formula so that it becomes =MAX([Profit]). Change
that this is a field (in this case the Profit the formulae for the minimum profit so that it becomes
=MIN([Profit]) and for the average profit so that it
field calculated at run time). Format this
becomes =AVG([Profit]). In the final control to count
control as Euro and set the Decimal
the number of cars, change the formula so that it
Places to 2.
becomes =COUNT([Profit]).

In the Property Sheet pane for the final Text

Box, change the Format back from Currency to
Move the cursor into the label for this
General Number. Set the Decimal Places for
text box and type in the Caption ‘Total
this control to 0. Check the layout and
Profit’. This can be entered in the label
calculations in Report View. The completed
or in the Property Sheet pane using the
calculations look like this.
Caption
Save the report. If you need to show evidence
of the formulae that you used, use screenshot
evidence of the calculated controls.

Designed and Presented by Ajiro Ndi 39

 AJIRO TECH - BEST IGCSE YOUTUBE CHANNEL
Note: You created the query in Question 11. To produce this
report, select the Create tab and click on the Report
If you are using screenshot evidence of calculated controls, make sure that each
Wizard icon. In the Tables/Queries box select the Ford
control is wide enough to show all of the formulae in full.
or Vauxhall query. Select all fields using the double
arrow key and then click on <Next> twice to open
the Sorting window. Use the drop-down lists to select
the Make field, then the Model field and, finally, the
SPrice field. For the SPrice field, click on <Ascending>
Open the file saved in Task 12. Copy and edit the report which contains the to the right of this field and it will toggle (change) to
<Descending>.
calculated fields to produce one that:
displays at the bottom of the report the maximum and minimum percentage
When the fields have been set as shown, click on . Set the Orientation to Landscape
profit for all the stationery items in stock
and run through the final stages of the wizard, giving this report a suitable name.
displays at the bottom of the report the average profit per item
This process is the same for other data types such as dates.
displays the number of items in stock
uses appropriate formatting for all data.

SORT DATA IN A REPORT

Although Access has the ability to sort data in both tables and queries, it is
easier to save the sorting until the data is produced in an Access report. Open the file saved in Task 13. Produce a new report from all the data that:
displays all the blue or black stationery items
fits within the width of a single page
is sorted into ascending order of colour and type, and then into descending order
Open the file saved in Question 24. Produce a report that: of description
displays all the data for the cars made by Ford or Vauxhall has your name in the report header followed by ‘Blue or black stationery items’.
fits within the width of a single page
is sorted into ascending order of make and model, then into descending order
of sale price
has your name in the report header followed by ‘Ford or Vauxhall’.

Designed and Presented by Ajiro Ndi 40