 |
|
|
|
 |
Turning Word into a pseudo-database by using
Mail Merge Query Options
|
Article contributed by Dave Rado
1.
|
“Mail Merge Query Options” is one of the most powerful features of
Word's Mail Merge facility.
Purists might argue that the power it gives
ordinary users isn't necessary because they should
use Access queries for this sort of thing and link the merge to the query. But
in my experience, many people who are very
comfortable working with Word and Excel find Access (or any full-fledged database application) very difficult to work with, and can get
the job done far more quickly and easily using a combination of Word and Excel. At
the end of the day, getting the job done is what matters. The vast
majority of the world's databases (in terms of number of databases, rather
than in terms of amount of data) are stored in Excel spreadsheets.
You can access Mail Merge Query Options by clicking on the “Merge” button on the Mail Merge toolbar, and then on the
“Query Options”
button; or by selecting Tools + Mail Merge + Query Options. However, I find
the feature so useful that I have added it to my Mail Merge toolbar.
One fact that is perhaps not immediately obvious is that when you create a query, the query is
saved with the mail merge Main Document. This is very powerful, because it means you can, for example, have a number of different standard mail merge letters
and labels all linked to the same Mail Merge Data Source, each with a different set of query options stored with them.
For example, a staff database (which could be an Excel Data Source) could contain a whole series of flags:
|
1.
|
A column flagging when each person's birthday is getting close, so that a standard
“happy
birthday” mailshot can use that in its query; and another column flagging whether or not the letter's already been sent (so they don't get mailed twice)
|
|
2.
|
A column showing their employee status, another showing their Line Manager, and if their employee status is
“Contractor”, a column showing their contract expiry date. A standard letter could be saved with a query on these columns in order to mailshot their line managers when the Contract is about to
expire
|
... and so on.
Here are a few examples of how you can make your mail merges more powerful using Query Options, starting with some very simple examples and getting more complex (and powerful) as we go on.
|
2.
|
If somebody didn't receive your letter, the simplest way to re-send
just their letter is to do a query on their first and last name as shown
below.
|
3.
|
Create a field (a column if you are using a Word or Excel Data Source)
called “SendAgain”. Put a
“Y” in that column against
the rows you want the letter to be re-sent to. Save, switch back to
Word, close the document and open it again to refresh it, and set up the Query Options dialog as follows:
Or, for example, if you want to send a follow-up letter to people who haven't
yet replied to the first mailshot, you could have a mail merge field called
“Replied”.
When a reply comes in, put a Y against the relevant record in the “Replied”
column. When it comes to time to chase up the stragglers, you can just set up
the Query Options to Replied/Not Equal to/Y, to send the follow-up letter
to those who haven't yet replied.
|
4.
|
Take the following scenario:
|
|
“We have a large mailing list that we use to post
invitations (our community and education department), and send information to various individuals and charity organisations. We have different groups (one for
xmas, invitations, etc); and one person can belong to many groups. At the moment they have to update
a lot of duplicates because a person exists in many groups. I would like have one master list with addresses and some kind of a code or index. I would like to stay away from databases.” |
|
|
This is easy. In your Data Source (your
“master list”),
create a column for each “group”; i.e. a column called
“XmasLetter”, etc. To
put someone into a particular group, simply put a “Y” in that
column against that person's row. Then set up all your standard letters
with the appropriate Query Options saved with the letter.
So for the standard Christmas letter, for example, the Query Options
dialog should read XmasLetter/ Equal to/Y.
When you save the letter, the Query Options are saved as well, so
from then on you can just open the appropriate standard letter whenever
you need to do a mailshot, click on the “Merge” button, and
you're done.
|
5.
|
You could have three Word documents attached to the same Data Source, each
saved with different Query Options:
|
|
The email would have its Query Options set to EmailAddress/Is not
blank.
|
|
|
The fax would have its Query Options set to EmailAddress/Is blank
and FaxNo/Is not blank.
|
|
|
The letter would have its Query Options set to EmailAddress/Is blank
and FaxNo/Is blank.
|
|
6.
|
Assuming you have an Excel spreadsheet containing names, addresses and
dates of birth:
|
1.
|
Create a column called “Birthday”. Use a formula such
as the following to calculate the person's birthday from their
date of birth:
=(DAY(C2)&"/"&MONTH(C2)&"/"&YEAR(TODAY()))
In the US you would swap the day and month round:
=(MONTH(C2)&"/"&DAY(C2)&"/"&"/"&YEAR(TODAY()))
The above formula assumes that the current row is Row 2, and
that the date of birth is in Column C. It takes the day and month
from their date of birth, shoves the current year at the end, and
displays the result. Having created the formula in one cell, you
can drag it down to Autofill the remainder of the column.
|
|
2.
|
Create another column called “BirthdayFlag”. Use a
formula such as the following to calculate whether their birthday
falls within the next 7 days:
=IF(TODAY()>DATEVALUE(D2),"Whoops, too
late!",IF(TODAY()+7>=DATEVALUE(D2),"Send
now",""))
If their birthday has already passed, it will display “Whoops, too late!”. Otherwise, if their birthday falls
within the next 7 days, it will display “Send now”, and
if it's more than 7 days until their birthday, it won't display
anything.
You could refine it further by adding a column called “LetterSent”,
and putting a Y in that column when you've sent the letter.
|
|
3.
|
In Word create a standard “birthday letter”, and in
the Query Options dialog, set the BirthdayFlag/Equal to/Y; and LetterSent/Is blank.
Create a second “happy
belated birthday” standard letter with
BirthdayFlag/Equal to/Whoops, too late!; and LetterSent/Is blank.
Open both letters once a week, merge, and you're done – again,
you only ever need to set the query options once.
|
The principle illustrated by this example can be used for all sorts
of useful mail merges – for instance, for sending reminders to
managers who have contractors working for them, when the contract period
is about to expire.
|
7.
|
Consider the following scenario:
|
|
|
“I have a large Excel database with some
missing information. I would like to create a form letter that
asks the recipient for the missing information.
What I'd like to do is write a letter that says, “For the patient
X, we are missing the following information: Date of birth, Diagnostic,
Dossier Number. For the patient Y, we are missing ... ”
etc., where Date of birth, Diagnostic, Dossier Number are all the names of columns in the Excel document.”
|
|
|
For this you would need to use IF fields in the form letter, with the
construction “If
the Date of Birth field is blank, display the text “Date
of birth”,
otherwise display nothing.”
Trying to have several such fields on a single line, separated by
commas, would require tortuous logic in order to get the IF fields to
suppress all unnecessary commas, so it's easiest to use a bulleted list
for this sort of thing:
For the patient X, we are missing the following information:
|
|
{ IF { MERGEFIELD DateOfBirth } = ""
"Date of birth" "" }
|
|
|
{ IF { MERGEFIELD Diagnostic } = ""
"Diagnostic" "" }
|
Note that, blank paragraphs which contain mail merge fields nested within an IF field are not suppressed when you merge.
You can get round this problem by merging to a new document rather than
directly to the printer, and then doing a Find and Replace replacing ^p^p
with ^p, to remove all blank paragraphs from the merged document.
Finally, use Query Options to strip out records for which no information is
missing. You could do this by setting the relevant fields in the Query
Options dialog to “Is
not blank”.
However, this only works with up to 5 fields (a limitation of the dialog). To get round this limitation, you can add a column in the
spreadsheet called “IsBlank”
containing a formula of the type:
=OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(C2),ISBLANK(D2))
This formula will display “True”
if any of the cells A2, B2, C2 or D2 are blank, and will display “False”
if none of them are blank. You can then simply set up the Query
Options dialog to merge with all records for which the “IsBlank”
field is equal to True.
|
8.
|
Take the following scenario:
|
|
|
“We are
having a recruitment drive. We have advertised in various media
and have also contacted various agencies. We are recording
all the details of any job applications we receive in an Excel
spreadsheet, together with interview dates etc.; but the spreadsheet has more than 30 columns, and
trying to make head or tail of the information does our eyes in.
What we need is a sort of database that allows me to see all the
information about a particular candidate laid out nicely, so it's
easy to read; and which lets me look at for example, only the
candidates who have an interview tomorrow. I want it to be easy to
look at this information both on screen and in a print-out. I'd
rather not use Access”
|
|
|
This is very simple to set up using a mail merge. Design a mail merge
form in Word (not with form fields, but laid out like a form), with the
30+ mail merge fields laid out logically on the page, making sure they
all fit on a page, and attached to the Excel spreadsheet.
Save multiple copies of the form, one for each report you need.
For instance, one could be called “InterviewsTomorrow.Doc”,
and it would have a query saved with it which filtered just those
records in which the interview date is tomorrow's date. To do this,
you could add a column in the Excel spreadsheet called “InterviewTomorrow”
containing a formula such as:
=IF(TODAY()+1=D2,
"Y", "")
.. where column D contained the interview date. This would display “Y” if the interview date was tomorrow, and display nothing
otherwise. Then you could set the Query Options to filter those records
for which the field “InterviewTomorrow” is equal to Y.
As part of the query you can also store how you want the information
to be sorted, so each query could be sorted differently (for the “InterviewTomorrow”
query
you might want to sort by interviewer, and then by interview time, for
example). In the Query Options dialog, click on the “Sort
Records” tab.
Once you've saved all the different versions of the form, each with
its own unique query, you can simply open the documents whenever you
like, to view or print the information, nicely laid out, and filtered
and sorted appropriately.
To see the records on screen, don't do a merge. Instead, click on
the ViewMergedData button on the mail merge toolbar to see the data on
screen without merging; and use the Next Record and Previous Record
buttons to skip through the records:
This is much quicker and more convenient than doing a full merge, and
gives you similar functionality to a conventional database application.
If you want to print the report, click on the Merge button and select
“Merge to printer”.
|
|
|
