ity

rs
ve

y
op
ni
U

C
ge

w
ie
id

ev
br
am

-R
-C

s
es
Chapter 17
y

Pr
op

Mail merge ity

C

rs
w
ie

ve

y
ev

op
ni
R

C
ge

w
ie
id

ev
br
am

-R
-C

s
es

LEARNING INTENTIONS
y

Pr
op

By the end of this chapter, you will be able to:

ity
C

• use, create and edit source data using appropriate software

rs
w

• create a master document structure

ie

ve

y
ev

• link a master document to a source file

op
ni
R

• specify rules selecting recipients and for managing document content

C
ge

• set up manual completion, automatic completion and calculated fields

ie
id

• use manual methods and software tools to ensure error-free accuracy

ev
br

• perform mail merge.

am

-R
-C

s
es
y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
e

w
g

ie
id

ev
br
am

-R
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
BEFORE YOU START

ie
id

ev
br
• Do you know how to create documents using a word processor?
am

-R
• Do you know how to create data sources using a database or spreadsheet?
-C

s
es
Introduction
y

KEY WORDS

Pr
op

A mail merge is the automatic process of merging data, mail merge: the automatic addition of data, such

ity
C

such as names and addresses, from a source file into a as names and addresses, from a source file into a

rs
w

master document, such as a letter. You can save time master document, such as a letter
ie

ve
writing the same letter to lots of different people when
source file: the file containing the data that will

y
ev

their data already exists in a data source.

op
ni
be merged into the master document
R

C
master document: the main document that will
ge

be used for all records

w
ie
id

ev
br

Data source Master document

am

-R
Name Address City Zip Store Account
-C

Louis xxxxxxxxxxxxx xxxx xxxx xxxx 0156595

s

Carlos xxxxxxxxxxxxx xxxx xxxx xxxx 0156594

es

James xxxxxxxxxxxxx xxxx xxxx xxxx 0156593 Auguest 2012

y

Pr

Dear :
op

We would like to inform you because

ity
C

your account - xxxxxxxx has qualified

you for a special gift.
rs
w

Please come in our store in xxxxxxx to

claim your price. Louis thank you for
ie

ve

being a loyal customer.

y
ev

op
ni

Sincerly
R

C
ge

w
ie
id

ev
br

James Carlos Louis

am

-R

xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx

xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx
August 2005 August 2005 August 2005
-C

Dear James : Dear Carlos : Dear Louis :

es

We would like to inform you because We would like to inform you because We would like to inform you because
y

your account - 0156593 has qualified your account - 0156594 has qualified your account - 0156595 has qualified
Pr

you for a special gift. you for a special gift. you for a special gift.
op

Please come in our store in xxxxxx to Please come in our store in xxxxxx to Please come in our store in xxxxxxx to
claim your price. James thank you for claim your price. Carlos thank you for claim your price. Louis thank you for
ity
C

being a loyal customer. being a loyal customer. being a loyal customer.

rs
w

Sincerly Sincerly Sincerly

ie

ve

y
ev

op
ni

Merged documents
R

C
e

Figure 17.1: Mail merge documents.

w
g

ie
id

ev
br
am

-R

377
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge
17.1 Mail merge letters

w
PRACTICAL ACTIVITY 17.01

ie
id
Create a master document that you will use to

ev
br
Create a master document
am write to customers of the company IT Distribution

-R
Inc. You should create the letter in a standard
structure letter format, including space for the recipient’s
-C

s
name and address. Your letter should invite
A master document is the main letter, email, memo, fax

es
the customer to participate in a survey with the
or other document that you can send to all recipients
y

opportunity to win one of ten prizes each worth

Pr
op

from the data source.

$250. Tell customers that the survey is available

ity
online and is available for two months from the
C

WORKED EXAMPLE 17.01 date of the letter.

rs
w
ie

ve
17.01 Learner letter.docx is a master document.

y
ev

It is a letter written to learner drivers in a driving

op
ni

school. The words in italics are the data that needs Create a source file
R

C
to be inserted about each learner driver and will be
You need to create a source file that contains the
ge

obtained from the data source.

w
data the computer will include in each mail-merged

ie
id

document. This source file usually consists of names

ev
br

and addresses and other information about the people

you are writing to.
am

-R
-C

WORKED EXAMPLE 17.02

es
y

17.02 Driving School.mdb contains a table

Pr
op

called Learner which includes the names, addresses,

telephone numbers and mobile numbers of learner
ity
C

drivers in a driving school.

rs
w
ie

ve

y
Source files can be in a variety of formats including:
ev

op
ni

• database table
R

Figure 17.2: Master document.

C

• database query
ge

Notice how a standard letter includes the letterhead • spreadsheet

ie
id

of the organisation sending the letter, the date of

ev
br

the letter and the full name and address of the • word-processed table
am

recipient of the letter. When you produce a letter on a

-R

• variable length text file (for example, comma-

computer, it should always have all the text (except the separated values)
-C

letterhead) aligned to the left.

s

• fixed length text file

es

email contacts.
y

•
Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
e

w
g

ie
id

ev
br
am

-R

378
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
PRACTICAL ACTIVITY 17.02 WORKED EXAMPLE 17.03

ie
id
1 Examine the structure of the following 17.01 Learner letter.docx has been linked to the

ev
br
data sources and identify which formats
am Learner table in 17.02 Driving school.mdb.

-R
they are in:
Select Table ?
-C

a 17.03 Student.csv

s
Created Type

es
Car 8/3/2015 11:41:39 AM 9/13/2013 1:15:38 PM TABLE
b 17.04 Student.rtf Instructor 8/3/2015 11:42:29 AM 9/13/2013 12:44:50 PM TABLE
y

Learner 8/18/2015 12:04:35 PM 9/13/2013 1:29:23 PM TABLE

Pr
op

Lesson 8/3/2015 11:43:55 AM 9/13/2013 1:48:39 PM TABLE

c 17.05 Student.txt

ity
C

d 17.06 Student.xlsx

rs
w

OK Cancel

e 17.07 Student.mdb (examine the

ie

ve
student table)

y
Figure 17.3: Database link.
ev

op
ni

f 17.07 Student.mdb (examine

R

C
the query) The records from the data source are identified in the
ge

master document in the recipient list.

w
2 Create a data source using a table in a word

ie
id

processor that will include the following

ev
br

fields (categories of information):

am

-R
a forename
-C

b surname
s
es

c email address.
y

Pr
op

3 Create three records in the data source.

ity
C

rs
w
ie

ve

KEY WORD
y
ev

op
ni

field: a category of information from the

R

data source
C
ge

Figure 17.4: Recipients.

w
ie
id

Link a master document to a

ev
br
am

-R

source file PRACTICAL ACTIVITY 17.03

-C

1 Open the master document 17.08 New

s

Your master document needs to know the source

es

location of the data. You therefore need to link the class.docx and link it to each of the
following data sources one at a time. Finish
y

master document to the data source.

Pr
op

by linking it to the query from the database.

ity
C

a 17.03 Student.csv
rs
w

b 17.04 Student.rtf
ie

ve

y
ev

c 17.05 Student.txt
op
ni
R

d 17.06 Student.xlsx
C
e

w
g

ie
id

ev
br
am

-R

379
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge

w
CONTINUED PRACTICAL ACTIVITY 17.04

ie
id
e 17.07 Student.mdb (link to the 1 Use the master document 17.08 New class.

ev
br
Student table)
am docx that you linked to the query in the

-R
database 17.07 Student.mdb. Insert merge
f 17.07 Student.mdb (link to the fields for forename, surname and class.
-C

query)

s
Open the letter that you wrote for IT

es
2
Open the letter that you wrote for IT Distribution Inc. and linked to 17.09 Sales
y

Pr
Distribution Inc. Use it as a master document
op

processing.mdb. Insert merge fields in the

and link it to the table Customer in 17.09 appropriate places.

ity
C

Sales processing.mdb.

rs
w
ie

ve
Perform mail merge using the

y
Set up fields
ev

op
ni

master document and data

R

Having a link from the master document to the source

C
file only tells the master document which file to use. The sources
ge

w
master document also needs to know which fields to put Now that you have entered the fields, the mail merge can

ie
id

into the document and where to place them. be completed. You need to tell the software to carry out

ev
br

the merge process. It is usually possible to preview the

am

-R results of the mail merge prior to printing or creating

WORKED EXAMPLE 17.04
a new document with the merged letters. It is also
-C

17.10 Learner letter with fields.docx includes the possible to merge the letters to an email address for each
s
es

merge fields from the Learner table in 17.02 Driving recipient.

y

school.mdb. The merge fields are highlighted

Pr
op

in yellow and have been inserted in place of the

WORKED EXAMPLE 17.05
placeholder names in Worked Example 17.01.
ity
C

17.10 Learner letter with fields.docx can be

rs
w

previewed. The data from the first record of the data

ie

ve

source 17.02 Driving school.mdb is highlighted in

y
ev

op
ni

yellow.
R

C
ge

w
ie
id

ev
br
am

-R
-C

s
es
y

Pr
op

ity
C

rs
w

Figure 17.5: Merge fields.

ie

ve

y
ev

When you eventually run the merge, the fields will be

op
ni

Figure 17.6: Merge preview.

replaced with data from the Learner table.
R

C
e

w
g

ie
id

ev
br
am

-R

380
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
PRACTICAL ACTIVITY 17.05

ie
id
1 Open 17.10 Learner letter with fields.docx and merge to a new document. Notice how all the records

ev
br
have been included so that there is one letter for every learner.
am

-R
2 As you have added merge fields, use the master document 17.08 New class.docx to merge to a
-C

new document.

s
es
3 Open the letter that you wrote for IT Distribution Inc. Merge the letters to send as emails to the email
y

address field in the Customer table.

Pr
op

ity
C

Edit the source data

rs
w

PRACTICAL ACTIVITY 17.06

ie

ve
You can edit the source data by making changes to the

y
ev

source file. However, if a master document that is linked 1 Open 17.08 New class.docx that you

op
ni

to the source file is open, then it is possible that the source have already merged with 17.07 Student.
R

mdb. Edit the data source to change Chloe

C
file will be locked and you won’t be able to edit it. You
ge

Carson’s surname to ‘Carlton’.

w
must remember to close the master document before
making changes to the source file. Any changes made to

ie
id

2 Open the letter that you wrote for IT

the source file will be visible in the master document.

ev
Distribution Inc, and linked to 17.09 Sales
br

processing.mdb. Edit the data source to

am

Some word processors allow the source data to be edited

-R
directly from within the word processor. change Carmella Wishman’s email address
to ‘carmella.w@wishman.com’.
-C

s
es

WORKED EXAMPLE 17.06

y

Pr
op

17.10 Learner letter with fields.docx has been

used to edit the data source and change Patricia Mail merge labels
ity
C

Thomas’ surname to Donaldson. You can use mail merge to create labels or similar
rs
w

documents such as business cards from a data source.

ie

ve

You can set up the document as a table with each cell

y
ev

representing a label, business card or similar. Each cell

op
ni

will contain the merge fields from one record from the
R

data source.
ge

w
ie
id

ev
br
am

-R
-C

s
es
y

Pr
op

ity
C

rs
w

Figure 17.7: Edit source data.

ie

ve

y
ev

Be careful when you edit the data source because

op
ni

other users may be using it and so any changes you

R

make would affect other users.

e

w
g

ie
id

ev
br
am

-R

381
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge

w
WORKED EXAMPLE 17.07

ie
id
17.11 Instructor Labels.docx has been set up to use You can now add merge fields to the first label.

ev
br
labels. It is set up to use the labels named L7263 which
am

-R
defines the measurements of the label.
-C

s
es
y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
ge

w
Figure 17.8: Setting up the labels.

ie
id

Figure 17.10: Adding merge fields to the labels.

ev
br

The label layout has been merged with the Instructors

am

You can update the rest of the labels to include the same
-R
table from 17.02 Driving School.mdb. A table is
merge fields as the first label. A preview of the labels
created for each label and a <<Next Record>> field is
-C

shows the merged content.

s

added to each of the cells in the table except the first

es

one. The <<Next Record>> field means that the next

y

record from the data source will be displayed so that

Pr
op

each cell contains data about a separate record from

ity
C

the data source.

rs
w
ie

ve

y
ev

op
ni
R

C
ge

w
ie
id

ev
br
am

-R
-C

s
es
y

Pr

Figure 17.11: Preview of merged labels.

op

Figure 17.9: Initial label merge structure.

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
e

w
g

ie
id

ev
br
am

-R

382
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
PRACTICAL ACTIVITY 17.07 WORKED EXAMPLE 17.08

ie
id
1 Create a new word-processing document 17.12 Learner letter with fill-in prompt.docx asks

ev
br
and set it up to contain labels. Link the
am the user to enter the name of the senior instructor

-R
document to the data source 7.07 Student. who will sign the letter.
mdb using the query Qry Student Teacher.
-C

s
Insert Word Field: Ask ?
Using merge fields, create a set of labels that

es
2
shows each student’s name, their class and
y

Prompt:

Pr
op

their teacher. Which senior instructor will sign the letter?

ity
C

3 Create a new word-processing document

that is linked to the Sales Rep table from
rs
w

17.09 Sales processing.mdb. Use the mail-

ie

ve
merge labels feature to create a business

y
ev

op
ni
card for each sales representative. You can
R

create business cards instead of labels by

C
selecting a business card page layout from
ge

w
the label options.

ie
id

ev
OK Cancel
br

Ask once
am

-R
17.2 Manipulating mail- Figure 17.12: Fill-in.
-C

s
es

merge documents The Fill-In prompt rule can be viewed as a merge field
y

code.
Pr
op

We would be grateful if you could email us at

Create prompts
ity
C

info@pass1st.info with your email address.

rs

You can use master documents several times and on

w

many different occasions. There may be some data that Yours sincerely
ie

ve

y
needs to be included within the master document that
ev

op
ni

is not part of the data source but will change each time
R

the mail merge is run. A prompt can be given to the { FILLIN "Which senior instructor will sign the letter?" \o }
C

user. This is a question that asks them what the data Senior Instructor
ge

should be.
ie
id

Figure 17.13: Fill-in code.

ev
br

KEY WORD
am

When you process the mail merge, the user is

-R

prompt: a question asked to the user which prompted for the name of the senior instructor.
-C

requires a response
s
es

Microsoft Word ?
y

Which senior instructor will sign the letter?

Pr
op

Ben Dean
ity
C

rs
w

OK Cancel
ie

ve

y
ev

op
ni

Figure 17.14: Prompt.

R

C
e

w
g

ie
id

ev
br
am

-R

383
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge
Note that Microsoft Word uses rules to control the way the same for every letter. However, by deselecting ‘Ask once’,

w
mail merge works. Fill-In is one of these rules. It is most you could have a different response for every recipient.

ie
id
commonly used to ask for one item of data that will be the

ev
br
am

-R
PRACTICAL ACTIVITY 17.08
-C

1 Open the master document 17.08 New class.docx to which you have added merge fields. Add a Fill-In

s
es
prompt for the name of the Head of Year.
y

Pr
2 Open the letter that you wrote for IT Distribution Inc. before you completed the mail merge process.
op

Add a Fill-In prompt for the date when the survey will close.

ity
C

rs
w

There are occasions when the same data needs to be Fill-In prompt. The Ask prompt stores the response
ie

ve
included more than once within a document. In these in a bookmark which can be placed in more than one

y
ev

op
ni
situations, an Ask prompt can be used instead of a location in a document.
R

C
ge

w
WORKED EXAMPLE 17.09

ie
id

17.13 Learner letter with ask prompt.docx You now need to reference the bookmark in the document.

ev
br

prompt asks the user to enter the date by which the You can do this by inserting a reference point.
am

information should be returned. You will notice that

-R
the difference from the Fill-In prompt is that the Ask
-C

prompt needs a bookmark to be defined. This has

s
es

been defined as ‘ActionBy’. You will also notice that

y

the default text has been completed. This is necessary

Pr
op

in order to be able to reference the bookmark.

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
ge

Figure 17.17: Reference point.

ie
id

ev
br

Here, you can see two reference points highlighted in

am

-R

Figure 17.15: Ask. yellow that have been inserted.

-C

You can view the Ask prompt rule as a merge field

es

code, but its location is not important because the

y

bookmark will be referenced in the required locations.

Pr
op

Yours sincerely
ity
C

rs
w

{ FILLIN "Which senior instructor will sign the letter?" \o }

ie

ve

Figure 17.18: Reference point text.

Senior Instructor
y
ev

op
ni

{ ASK ActionBy "What date should the information be

returned by?" \d "Return Date Here" \0 }
R

C
e

Figure 17.16: Ask code.

g

ie
id

ev
br
am

-R

384
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
CONTINUED

ie
id
You can also view these as merge field codes.

ev
br
am Microsoft Word ?

-R
What date should the information be returned by?
20 January 2017
-C

s
es
y

Pr
op

OK Cancel

ity
C

Figure 17.19: Reference code. Figure 17.20: Prompt.

rs
w
ie

ve

y
When you process the mail merge, you are prompted If you look carefully at the field code for the Ask
ev

op
ni

for the information return date. prompt you will notice that it includes the name of the
R

bookmark. This can be useful in other ways because you

C
can reference the data by other rules in the same way
ge

w
that data in fields can be referenced.

ie
id

ev
br
am

Automatically select the

-R
PRACTICAL ACTIVITY 17.09
-C

Open the master document 17.08 New class.docx. required records

s
es

You will already have added merge fields to this.

Data sources can often have thousands of records. The
y

Instead of a Fill-In prompt, add an Ask prompt

Pr
op

for the name of the Head of Year and assign a document that is being produced may not need to be
bookmark. Insert references to the bookmark at merged with every record. It is therefore possible to set
ity
C

the end of the document where the Head of Year conditions to select which records will be included in the
rs
w

will sign and a new sentence that will inform the mail merge by using a filter.
ie

ve

parents who the Head of Year will be.

y
ev

op
ni

KEY WORD
R

filter: selecting records from the source file

ge

based on conditions
w
ie
id

ev
br
am

-R
-C

s
es
y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
e

w
g

ie
id

ev
br
am

-R

385
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge

w
WORKED EXAMPLE 17.10 WORKED EXAMPLE 17.11

ie
id
17.14 Learner letter with filter.docx has filtered 17.15 Learner letter with sort.docx has sorted the

ev
br
the records so that the letter will only be sent to male
am records by surname and if any surnames are the same

-R
learners whose records haven’t been updated since then by forename.
1 January 2015.
-C

s
es
y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni

Figure 17.23: Selecting sort order.

R

C
Figure 17.21: Filter.
ge

w
ie
id

The recipient list now only includes the filtered

ev
br

records, so the letter will only be sent to those PRACTICAL ACTIVITY 17.10
recipients.
am

-R
1 Open the master document 17.08 New
class.docx that you have added merge
-C

fields. Set the filter so that the letter is only

es

sent to students in class 9F.

y

Pr
op

2 Change the filter so that the letter is sent to

students in classes 9F or 9B.
ity
C

3 Change the order of merged letters so they

rs
w

are sent to students in surname order. If any

ie

ve

students have the same surname, then they

y
ev

op
ni

should be sorted by forename.

R

4 Open the letter that you wrote for IT

ge

Distribution Inc. Set the filter so that the

w

merged email is only sent to customers who

ie
id

have agreed to receive marketing and live in

ev
br

Figure 17.22: Filter records. the state of California (CA).

am

-R

5 Change the order of the merged email so

-C

they are sorted by postcode or ZIP code.

s
es

Sort the merged documents

y

Pr
op

The data source may not be sorted in the order in which

the user wants to merge the documents. Rules for managing document
ity
C

content
rs
w
ie

ve

Data sources are often fixed and cannot be changed.

y
ev

Therefore, any data manipulation may need to take

op
ni

place in the merge master document. The IF…THEN...

R

ELSE rule can be used to insert text conditionally based

e

on data within the data source.

w
g

ie
id

ev
br
am

-R

386
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge
The other option you can use is a nested IF...THEN…

w
WORKED EXAMPLE 17.12 ELSE rule. This involves using another IF...THEN...ELSE

ie
id
17.16 Learner letter with condition.docx is to rule as the ELSE part of the original IF...THEN...ELSE

ev
br
have a new sentence that asks users to confirm their
am rule. You can only manipulate this in field code view.

-R
gender has been stored correctly. You can see here
how the IF statement was set up. TIP
-C

s
es
Note that the nested IF...THEN… ELSE rule do
y

not work in Open/Libre office

Pr
op

ity
C

rs
w

WORKED EXAMPLE 17.14

ie

ve

y
ev

17.18 Learner letter with nested conditions.docx

op
ni

has used a NESTED IF by entering the IF...THEN...

R

C
ELSE rules within the ELSE parts of previous rules.
ge

w
Figure 17.24: IF…THEN…ELSE.. Our records show that your gender is { IF { MERGEFIELD
Gender } = "M" "Male" }{ IF { MERGEFIELD Gender } = "F"

ie
id

"Female" }{ IF { MERGEFIELD Gender } = "" "not recorded in

ev
br

This can be seen in field code view as shown here.

our records – please confirm your gender" }.
am

Our records show that your gender is { IF { MERGEFIELD

-R
Gender ] = "M" "Male" "Female" }. Figure 17.27: Nested IFs.
-C

Figure 17.25: IF…THEN…ELSE code.

es

It achieves the same outcome as the previous example,

y

but is a bit more complex. The speech marks need to

Pr
op

be in exactly the right places, as do the curly brackets.

This will work for situations where there are only two
ity
C

You can use AND or OR operators within the IF...

alternatives. However, when more than two alternatives THEN...ELSE rule. You can also include text from
rs
w

are required, you will need to use a different method. files as the outcome or pictures as the outcome. You
ie

ve

One option is to use a series of IF...THEN...ELSE rules may want to research how these can be achieved.
y
ev

to cover each eventuality.

op
ni
R

WORKED EXAMPLE 17.13

ge

PRACTICAL ACTIVITY 17.11

ie
id

Some records may not have the gender recorded. In

1 Open the file 17.19 Vehicle Data Sheet.
ev

this situation, the letter should state ‘your gender is

br

not recorded in our records – please confirm your docx which has been merged with the Car
am

-R

gender’. There are now three options: F, M or [blank] in table from 17.02 Driving School.mdb.
17.17 Learner letter with three conditions.docx.
-C

2 Change the field for the Transmission so that

s

ELSE cannot be used because it would apply the it automatically displays ‘Manual’ instead of
es

second outcome to both of options two and three. ‘M’ and ‘Automatic’ instead of ‘A’.
y

Pr

Here, three separate IF...THEN...ELSE rules have

op

been used without defining the ELSE part. 3 Open the letter that you wrote for IT
ity
C

Distribution Inc. Add a sentence that tells

Our records show that your gender is { IF { MERGEFIELD customers who live in California (CA) that
rs
w

Gender } = "M" "Male" }{ IF { MERGEFIELD Gender } = "F" they have a special discount. If they live in
ie

ve

"Female" }{ IF { MERGEFIELD Gender } = "" "not recorded in

California then the sentence should read
y

our records – please confirm your gender" }.

ev

op
ni

‘As you live in California, you are entitled

R

Figure 17.26: Separate IFs. to a special 10% discount until the end of
C

this month.’
e

w
g

ie
id

ev
br
am

-R

387
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge
Rules for selecting recipients

w
CONTINUED

ie
id
One method of deciding which records will be included a Add a rule to skip records for customers

ev
br
from the data source is to use a filter. Another method
am who have chosen not to receive

-R
you can use is a rule, which is a merge field code that will marketing (this is a Boolean field that
control which records will be omitted. The rule is called can be TRUE or FALSE).
-C

SKIP IF and this will omit the record from the mail

s
b Add additional rules to skip records for

es
merge if the conditions are met.
customers who are based in New York
y

Pr
(NY), California (CA) or Texas (TX).
op

WORKED EXAMPLE 17.15

ity
C

17.20 Learner letter with selection codes.docx

rs
w

includes a SKIP IF rule that states that if the gender

ie

ve
field is equal to “M” then the record should be
17.3 Arithmetic

y
ev

skipped.

op
ni
R

operators
U

C
Insert Word Field: Skip Record If ?
ge

Field name:

w
Gender There are occasions when the merged data needs to have

ie
id

Comparison: a calculation performed on it. For example, a discount

ev
br

Equal to might be offered when writing or the tax might need

Compare to:
to be calculated on a price. It may not be possible to
am

M
-R
perform this calculation within the data source itself
OK Cancel
and so a calculation can be performed on the merge
-C

field. When a calculation is performed, it is known as a

es

Figure 17.28: Skip If set up. calculated field.

y

Pr
op

SKIP IF a rule is inserted into the document.

KEY WORD
ity
C

rs

calculated field: an arithmetic calculation on a

w

field from the data source

ie

ve

Figure 17.29: Skip If code.

y
ev

op
ni
R

The rule should always be inserted at the beginning of the WORKED EXAMPLE 17.16
ge

document. When using Microsoft Word, you will need to

The file 17.21 Instructor letter with calculation.
ie

complete the merge to see which records have been skipped

id

as they will still show when previewing the merge. docx has been merged with the Instructor table from
ev
br

17.09 Sales processing.mdb.

am

-R

PRACTICAL ACTIVITY 17.12

-C

1 Open the master document 17.08 New

es

class.docx.
y

Pr

a Add a rule to skip records for class 9B.

op

b Add a second rule to also skip records

ity
C

for class 9F.

rs
w

2 Open the letter that you wrote for IT

ie

ve

Distribution Inc. and remove any filters that Figure 17.30: Instructor letter.
y
ev

you have applied.

op
ni
R

The formula option has been selected and a formula

C

added to multiply the charge by 0.2. The number format

e

has been set to currency with two decimal places.

g

ie
id

ev
br
am

-R

388
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
CONTINUED CONTINUED

ie
id
The merge codes can be hidden (ALT+F9) to view the

ev
br
am result of the calculation.

-R
-C

s
es
y

Pr
op

ity
C

rs
w

Figure 17.35: Preview of calculation.

ie

ve
Figure 17.31: Setting up a calculated field.

y
ev

op
ni

This initially gives an error because ‘charge’ doesn’t

R

C
mean anything. PRACTICAL ACTIVITY 17.13
ge

w
1 Open the file 17.19 Vehicle Data Sheet.docx

ie
id

which has been merged with the Car table

ev
br

from 17.02 Driving School.mdb.

am

-R
2 Add a calculated field that states when the
-C

Figure 17.32: Bookmark error. MOT (vehicle safety test) is due. The MOT
s

is due three years after the vehicle was

es

registered.
y

The field codes can be edited using Alt+F9 and the

Pr
op

Charge merge field can be inserted in place of ‘charge’ 3 Open the file 17.21 Instructor letter with
ity

within the calculation.

C

calculation.docx which has been merged

with 17.09 Sales processing.mdb.
rs
w
ie

ve

4 Add a sentence and a calculated field that

y
ev

tells the driving instructor their maximum

op
ni

number of hours available during weekdays

R

(multiply the Max Hours by five).

ge

5 Challenge: add a sentence and a calculated

ie
id

field that tells the driving instructor their

ev
br

maximum number of hours available during

the week. This should include weekends for
am

-R

those who have opted to work weekends (if

Weekends field set to TRUE).
-C

Figure 17.33: Including a merge field in a calculation.

s
es
y

The merge field is now included within the calculation.

Pr
op

Statement of Tax Date and time fields

ity
C

Your hourly charge is £{ MERGEFIELD Charge } You can include the current date or time in a document.
rs
w

This doesn’t have to be a merged document but can be

ie

ve

The amount of tax we collect from this is { ={ MERGEFIELD any document. Each time you open the document or
y
ev

Charge }*0.2 \# "£#,##0.00;(£#,##0.00)" }

op
ni

the field is refreshed, the current date or time will be

R

Figure 17.34: Calculated field with merge field. displayed.

C
e

w
g

ie
id

ev
br
am

-R

389
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge
17.4 Document properties

w
WORKED EXAMPLE 17.17

ie
id
You can choose a variety of date and time formats.

ev
Properties of the document can be included

br
Ticking the Update automatically option will ensure
am automatically including:

-R
the date or time is automatically updated.
• filename
-C

s
• author

es
• document title
y

Pr
op

• creation date

ity
C

• number of pages.

rs
w
ie

ve
PRACTICAL ACTIVITY 17.15

y
ev

op
ni

Open a word-processing file that you have

R

C
created and that includes more than one page.
ge

Experiment by adding the document properties

w
into your document.

ie
id

ev
br

Figure 17.36: Inserting a date field.

am

-R
Embedding data
-C

You can embed data from another document.

es

Embedding means that the data remains in the original

y

PRACTICAL ACTIVITY 17.14

Pr

source document but can be shown in a word-processed

op

1 Open the file 17.19 Vehicle Data Sheet. document. Any changes you make to the source
ity
C

docx which has been merged with the Car document will be automatically updated in the word-
processed document.
rs
w

table from 17.02 Driving School.mdb.

ie

ve

2 Add a sentence that states the date and

y
ev

KEY WORD
op
ni

time the document was printed, for example

R

‘This document was printed on <<date>>

U

at <<time>>’. The date and time should embedding: importing data from a data source
ge

so that any changes to the data source are shown

w

automatically update.
in the new document
ie
id

3 Open the letter that you wrote for IT

ev
br

Distribution Inc. Add the current date to the

am

-R

beginning of the letter so that it updates

automatically. Embedding tables
-C

You can embed data in tabular format within a

s
es

document. Any changes you make to the data in the

y

data source will be updated in the document.

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
e

w
g

ie
id

ev
br
am

-R

390
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
WORKED EXAMPLE 17.18 WORKED EXAMPLE 17.19

ie
id
17.22 Attendance Report.docx is a report that has 17.22 Attendance Report.docx is a report that has

ev
br
been written to summarise the attendance of students
am been written to summarise the attendance of students

-R
in a school. The attendance data has been embedded in a school. The chart showing attendance data has
from the spreadsheet 17.23 School attendance.xlsx. been embedded from the spreadsheet 17.23 School
-C

attendance.xlsx. Here is the chart.

s
es
y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
ge

Figure 17.37: Paste and link.

w
ie
id

Any changes you make to the selected data in

ev
br

17.23 School attendance.xlsx will be updated

am

-R
automatically in 17.22 Attendance Report.docx
the next time it is opened. Figure 17.39: Embedding a chart from a spreadsheet.
-C

You can also update the data while the main

es

Any changes you make to the chart in 17.23 School

document is open by clicking Update Link.
y

attendance.xlsx will be updated automatically in

Pr
op

17.22 Attendance Report.docx.

ity
C

rs
w
ie

ve

PRACTICAL ACTIVITY 17.16

y
ev

op
ni

1 Create a new word-processing document.

R

Write a brief report summarising the results

C

of the votes for head prefect at your school.

ge

The report should include embedded data

ie
id

from the table in 17.24 Graphs and charts.

ev
br

xlsx and an embedded pie chart from the

same data source.
am

-R

2 Save and close your report.

-C

Figure 17.38: Updating embedded data.

s
es

3 Open 17.24 Graphs and charts.xlsx and

change the number of votes for Adrian
y

Pr
op

Smith from 4 to 40.

ity
C

4 Save and close the spreadsheet.

Embedding charts
rs
w

Similarly, you can embed data into a document from 5 Reopen your report and check that the data
ie

ve

and chart have updated automatically to

y

a graph or chart. Any changes to the original graph or

ev

show 40 votes for Adrian Smith.

op
ni

chart will be updated automatically in the document.

R

C
e

w
g

ie
id

ev
br
am

-R

391
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge
• printing a copy of the document to read it instead
17.5 Error-free accuracy

w
of reading it on screen

ie
id

ev
br
and proofreading
am • reading the document out loud so errors that may
not be spotted visually are heard

-R
You should carry out a number of checks before • using a blank piece of paper to cover up the rest
completing a mail merge.
-C

of the document so each line can be checked

s
es
• Run a spell check and grammar check on the individually
y

master document.

Pr
• searching for common errors such as ‘there’ instead
op

• Visually check for any errors on the of ‘their’.

ity
C

master document. Even if you run a spell check, it may not spot all the

rs
w

• Visually check for any errors on the preview or errors. These can include words that sound the same
ie

ve
merged documents. but are spelt differently, for example, stationery and

y
ev

stationary. The spell check will not be able to identify

op
ni

• Check that the correct records have been included problems with layout, such as spaces that are missing
R

in the filter.

C
or have been added by mistake. Errors in names are
ge

Visual checking could include: not spotted by a spell check and field codes will not be

w
checked by the spell check. A visual check will also be

ie
id

• reading the document from start to finish to see if required to see if all merge fields have been included.

ev
br

there are any errors in grammar or spelling

am

-R
WORKED EXAMPLE 17.20
-C

s
es

17.25 Learner letter with errors.docx includes several errors.

y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
ge

w
ie
id

ev
br
am

-R
-C

s
es
y

Pr
op

ity
C

Figure 17.40: Errors.

rs
w
ie

ve

The grammar and spell checks have spotted some errors: ‘a anual’ should be ‘an annual’, ‘wood’ should be ‘would’
y
ev

op
ni

and ‘greatful’ should be ‘grateful’.

R

The errors highlighted in yellow require visual checking as the grammar or spell checks have not identified them:
C
e

‘record’ should be ‘records’, ‘you’re’ should be ‘your’ and ‘Deane’ should be ‘Dean’.
w
g

ie
id

ev
br
am

-R

392
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
17 Mail merge

op
ni
U

C
ge

w
CONTINUED

ie
id
The errors highlighted in blue also require visual checking but these may not be spotted until data is actually

ev
br
merged. Here, you can see what the data would look like when merged.
am

-R
-C

s
es
y

Pr
op

ity
C

rs
w
ie

ve

y
ev

op
ni

Figure 17.41: Merge errors.

R

C
ge

w
There is no space between the forename and surname and there is an additional space after the mobile number.

ie
id

The criteria for the filter are that letters should be sent to male learners whose records haven’t been updated since 1

ev
br

August 2015. This should mean letters going to 11 learners. However, only four letters will be produced.
am

-R
Gender Telephone Mobile Licence number Date updated
-C

s
es

M 0555 555 511 0777 777 720 KRIS9999999PA9XX 5/24/2014

y

M 0555 555 513 0777 777 718 SARR9999999DA9XX 5/24/2014

Pr
op

M 0555 555 516 0777 777 715 DRAK9999999RA9XX 5/24/2014

ity
C

M 0555 555 526 0777 777 774 LANT9999999PE9XX 5/6/2014

rs
w

Figure 17.42: Filter errors.

ie

ve

y
ev

op
ni

This has been spotted using a visual check. The error was due to the filter requiring an American layout of date
R

which should be 8/1/15, rather than 1/8/15 that had been used. When corrected, the 11 learners are included.
C
ge

Gender Telephone Mobile Licence number Date updated

ie
id

0555 555 557 0777 777 724 HARI9999999RA9XX 6/8/2015

ev

M
br

M 0555 555 511 0777 777 720 KRIS9999999PA9XX 5/24/2014

am

-R

M 0555 555 512 0777 777 719 SMIT9999999SA9XX 6/8/2014

-C

M 0555 555 513 0777 777 718 SARR9999999DA9XX 5/24/2014

s
es

M 0555 555 516 0777 777 715 DRAK9999999RA9XX 5/24/2014

y

M 0555 555 517 0777 777 714 BLAC9999999SA9XX 6/8/2014

Pr
op

M 0555 555 518 0777 777 713 DREW9999999JA9XX 6/8/2014

ity

M 0555 555 519 0777 777 712 BROW9999999DA9XX 6/8/2014

C

M 0555 555 523 0777 777 777 PETE9999999PA9XX 6/8/2014

rs
w

M 0555 555 525 0777 777 775 HARR9999999PA9XX 6/8/2014

ie

ve

M 0555 555 526 0777 777 774 LANT9999999PE9XX 5/6/2014

y
ev

op
ni
R

Figure 17.43: Filter correction.

e

w
g

ie
id

ev
br
am

-R

393
-C

s
es

Copyright Material - Review Only - Not for Redistribution

 ity
rs
ve

y
CAMBRIDGE INTERNATIONAL AS & A LEVEL IT: COURSEBOOK

op
ni
U

C
ge

w
PRACTICAL ACTIVITY 17.17

ie
id
1 Open the master document 17.26 New class errors.docx. Check the master document and merged

ev
br
letters for accuracy. The merged letters should be sent to students in classes 9F or 9B.
am

-R
2 Change the filter so that the letter is sent to students in classes 9F or 9B.
-C

s
es
y

Pr
REFLECTION
op

ity
C

1 How long would it take you to write 100 personalised letters to customers without using mail merge?

rs
w

2 What do you think are the advantages of using mail merge?

ie

ve
How could you apply what you have learnt about mail merge to your life? Are there any occasions for

y
3
ev

op
ni

you when mail merge might be useful, for example in your social life?
R

C
4 When you created a mail merge document, did you make any errors? If so, what did you do to
ge

correct them?

w
ie
id

ev
br
am

REVIEW PRACTICAL ACTIVITY

-R
1 Create a new word processing document and link it to the Product table in 17.09 Sales
-C

processing.mdb
es
y

2 Create a product information sheet using mail merge fields from the Product table. The product
Pr
op

information sheet should include the Product ID, Product Name, Quantity Per Unit, Unit Price, Units in
ity

Stock, Reorder Amount, Units on Order and Reorder Level.

C

rs
w

3 Sort the data so that it will be merged in order of Product Name.

ie

ve

4 The product information sheets should only be printed for stock that has NOT been discontinued.
y
ev

op
ni

5 At the top of the product information sheet should be the date the sheet is printed. This should
R

automatically be the current date.

ge

6 At the bottom of the document should be the name of the person who printed the document. This
ie
id

should be populated using the word field FILL IN.

ev
br

7 Use the word field SKIP IF to skip any products that are in Category ID 3.
am

-R

8 If the Unit Price is above $60 then the phrase ‘HIGH VALUE ITEM’ should be displayed.
-C

9 The Sale Price should be displayed. The Sale Price is 30% on top of the Unit Price.
es
y

10 The total value of stock for each product should be displayed.

Pr
op

11 Merge the product information sheets to a new document.

ity
C

rs
w
ie

ve

y
ev

op
ni
R

C
e

w
g

ie
id

ev
br
am

-R

394
-C

s
es

Copyright Material - Review Only - Not for Redistribution