Merging Documents in Office 2007 . Introduction: This seminar is designed to assist you in the development and use of merge files so that you can produce such things as letters, labels, envelopes and so on without having to retype each one individually. It is an overview to be sure but with enough detail to walk you through the creation of the files you need to accomplish your task. It is recommended that after participating in this seminar that you practice with data files of your own making using this handout to guide you initially and refer to in the future.

Main Document

Data Document

Letter Form Envelope Label Memo Etc.

Name Address City State Zip

Definitions: When working with a data file (or database), it is important to understand the 1 terms that will be used to identify the individual component parts. A telephone book is the universally common example of a database that everyone is familiar with. In it, each person has a listing that includes his or her name, address and telephone number. Each of those pieces of information is contained in a “Field”. The collection of all the fields for each person’s listing is referred to as a “Record”. A subsequent list of all the records in a given city or “phone area” is then referred to as a “Data File” or “Database”. By definition then, “a Database is a collection of information about persons, places or things that have something in common.” That common element is generally one or more fields of information within each record. In our example; the telephone number is the common piece of information. It is the reason the collection has been created in the first place and is therefore the significant field.

2 3 4 5

The Data Document: To be useful, the data in our telephone book example is arranged so that the individual records can be carefully and well defined. To make this task easier, a grid or table is used with each column designated for the individual fields and each row designated for the individual records. Pre Dr Mr Mr Mr

FirstName Donald Huey Dewey Louie

MI H

LastName Duck Duck Duck Duck

Suf DDS PhD

Add1 1234 Quacker Way 1519 Duckpond Dr 9115 Gooseland St 9753 Daffy Ave

Add2 Lot 2A Apt 204

City Duckberg Duckberg Gooseland Duckberg

State NE NE NE NE

PostalCode 68000 68000 68040 68000

HomePhone 343-1496 292-9604 331-6161 593-5777

Notice that the first row contains the names of the fields and that each “Field Name” is unique. This is called a “Header Row” and tells the computer (and us) what the individual fields are. When you create field names for your data file, notice that the name is short but descriptive of the data the field will contain. It is important to understand that there is no punctuation or spaces in the field names. This insures the computer will be able to process your data file without difficulty. Later, during a merge, the “Field Names” will be used to position the data in the main document as needed.

The Main Document: The main document is often referred to as the “Primary File” or the “Master File” because it is the document that is repeated over and over to form the individual output documents. It is the framework if you will, of the message and will receive information from the “Data Document” to create individualized forms for each record. When the main document is created, you will insert the field names in your text where you want the data to occur in the final form letters, labels or envelopes. It is also here that fancy formatting is applied such as bold and text color.

Steps to a mail merge (The easy way)

1. Create the master letter just like any other Word document but leave space for the inside address and other elements that will be added by the merge. For example, Dear (space), Here we have placed the salutation for the letter, left space for the name and put a comma. Later, we will put a field code for the name in the space. 2. Either create or make readily available an existing data file. For a simple name and address listing, I would suggest Microsoft Excel. For very large database applications, Microsoft Access or an SQL database would be better. The example above uses a 433 name database in Microsoft Excel. 3. Assuming the first two steps were completed yesterday and you are ready to do the merge today; open the Main document in Word like you would open any other document. 4. Right Click on the MAILINGS tab to get the Mailings ribbon bar. See the top of this document to see what it looks like and for an explanation of the significant buttons. 5. Click on the Start Mail Merge 1 button. The second button, Select Recipients 2 is what attaches your Data Source (in this case the 433 name database). Click it and the file manage will open allowing you to navigate to and select your data source. 6. Once the database has been accessed skip over to the Insert Merge Field 3 in your database such as Title, FirstName, MI, LastName and so on.

button. It will open a list of the fields

7. To save time, I normally double click on each field I want in the Main file and just let them all run together. That saves closing the field list after inserting each field so that I can add spaces, commas and the like. Then, once they are all in the main letter, close the field list dialogue box and begin to add the spaces etc. between the fields. NOTE: Be careful to include the double chevrons because they are part of the field code. For Example; my inside address would look like the following to start: 8. When I finished it would look like this: Note the spaces between the >> ,

9. I would continue and add fields in the salutation, perhaps a RE line and the body where I wanted the data to appear. In this example, the police letter signature box contains the name of the city. The signature block would then be: Judge Roy Bean Police Commissioner When merged, the city for this individual record will replace the field code. So it might end up Commissioner or Bellevue Police Commissioner and so on.

The Omaha Police

10. When ready, click on the Merge to New Document 4 button Then select “Edit Individual Documents”. In this example, you will end up with 433 separate pages in a new document, each an individualized letter.

Note: It is a bad idea to select “Merge to Printer” as you will have no opportunity to edit documents or check for errors. The entire merge will go directly to the printer.

The Master Document ready to merge February 18, 2010 «Title» «FirstName» «LastName» «Add1» «Add2» «City», «St» «Zip»

Dear «Title» «LastName»: It has come to our attention that you are having far too much fun with parties at your home until all hours of the night. This noise factor has annoyed your neighbors and they have come to us to do something about you. Further, the cars of your guests are not only parked in front of neighbor’s houses but in their driveways and for goodness sake, on their yards. Needless to say, this has the entire neighborhood up in arms. You are hereby advised to cease the parties immediately. If you do not do so, the «City» Police Department will be forced to dispatch our Cobra Attack Helicopter and nuke your house. When it finishes with you, your house, cars and everything you own will be reduced to ashes. Have a nice day. The «City» Police Department

Judge Roy Bean, Police Commissioner

The First 10 Records of the data file NO 1 2 3 4 5 6 7 8 9 10

Title Mr Ms Mr Ms Ms Ms Mr Ms Mr Ms

FirstName Philip Lisa Russell Gina Kristin Jackie Rolland Denise James Barbara

MI A A L M K K F M

LastName Adams Addleman Addleman Agar Aguilera Albright Albright Anderson Anderson Armstrong

Suf

Add1 585 Cedar St 8212 So 88th Plz 505 Woodbine Circle 6411 S 109 St 1311 Hancock St 365 S. 4th St 2211 Calvin Dr 427 N Grandview Ave 4848 So 180th St 3101 Washington St

Add2 #8

#101

City Louisville Ralston Omaha Bellevue Papillion Louisville Bellevue Omaha Bellevue Bellevue

St NE NE NE NE NE NE NE NE NE NE

Zip 68059 68128 68046 68005 68059 68046 68135 68005

Adding Attributes for effect Sometimes you want to add a little flair or emphasis to your final merged documents such as having the addressee’s name in Bold or underlined or perhaps even color. Just like normal text, a field code can be selected and carry the formatting that you want. Then, when the document is merged with the data source, the formatting will appear on the data that was brought in. For example Dear Mr. Smith, is rather bland but Dear Mr. Smith, draws attention. First we need to understand the difference between the “Field Name” and the “Field Code”. This is the Field Name from the Data Source This is the Field Code in Word for Merging (includes the Chevrons >) When you would like your documents to have text attributes applied when the data comes in, then you will need to select the entire field Code and not just the field Name before we apply those attributes. This can be a little tricky but it is easier if you start by putting your mouse to the right of the field code and drag left making sure you select the name AND the chevrons like this: and not this . Then, once selected, add the attributes to the field code that you want like any other text. When the documents are merged, the data that comes into that field will have the attributes on each of the output letters.

Merging Selected Records 1. Click on Edit Recipient List button to open the Selection dialogue box. You have three choices: 2. Check individual records to select them 3. Drop the selection arrow for a field and select Advanced (i.e. City) 4. Provide the criteria for the data wanted and click on OK (i.e. Bellevue) This feature allows you to selectively merge based on criteria for this particular mailing. It saves having to create a special data file which is preferable because all your data stays in one place.

Using Special Controls (Rules)

3A

Special

Conditions can be added to provide special effects during a merge. Most are relatively self explanatory and can be used after a small bit of experimentation. For example, The If…Then…Else code allows two different text phrases to be inserted at a specific point based on the content of a specific field for each merged record. In the example above, the If…Then…Else code would be inserted at any point in the letter. It would not show as a code as such but rather the default text (the else condition) would be inserted at that point. Then, during the merge, if a record’s city field contained “Gretna” the text would read “Blackhawk Attack Helicopters”, but if it contained anything else (including nothing at all), the else condition would be inserted and the text would read “Stealth Bombers”.

Making Labels: 1. Open Word and/or open a New document 2. Select the Mailings tab just like you did for letters above. 3. Click on the Start Mail Merge button. Do not use the Create buttons to the left for labels or envelopes 4. Select Labels from the resultant dialogue box. 5. Select the type of Label you want to use (Avery 5161 here)… note: stick to standards 6. Click OK and Word will generate a Label form like this one. 7. Follow the steps from page 1 to insert your fields in the first label ONLY (red arrow). 8. After your fields are in place on the first label and formatted the way you want them, click on the 9. Update Labels button. Word will generate an entire sheet of the same label with identical formatting. 10. Click on the Merge to New Document button, check them over and send them to the printer. Note: If you make changes to the master label, be sure to hit “Update Labels ” again to apply the changes to all.

Making Envelopes: 1. Follow the same steps as you did for labels except select Envelopes in the dialogue box. 2. Select the type of envelope you are using, (probably a number 10 Business) and click on OK. 3. Type the Return Address if you want one or leave blank if they are pre-printed. 4. Click in the area where the address would normally go and a gray Text Box will appear. a. This is the text box that will contain your Field Codes.

5. Insert your field codes and format the text the way you want. 6. Click on the Merge to New Document button, look them over and send to a printer. Note: You can move the text box with the address if you want but I would not recommend it. The automatic readers at the post office are set for this position. Moving may cause it to be hand sorted.

Special Note: Often we have a field that has no data in some records. Here is how to get rid of an unwanted double space when the field is empty. Click the Rules button and select the If…then…else option. That will open up the Insert Word Field dialogue box shown below and we can set the conditions for the arguments. Note that I have selected the MI field, set the comparison to “Greater than” and compared it to zero “0”. What you cannot see in this shot is that the next box says “Insert this text: and I have typed in a single space In other words, if the MI field does contain data, then insert a space before it. If it does not contain data, do not insert a space. You could also choose “Equal To” and leave the comparison blank which the field would be if there were no middle initial. Experiment with some play letters and a small database with the Word codes to see how they work. Do not forget that the help system is exceptional and gives you access to the entire web for information.

Another note: In word you can generate dummy text for practice by using an unknown function. Type =rand(3,5) in lower case and no spaces and then press enter. It will give you 3 paragraphs with 5 lines each. You can use different numbers of course and then go in and apply spacing to your paragraphs as desired.

Tips for Database Management Tip Number One: Never design a database while sitting at a computer. The mechanics of the computer will divert your attention from the all important structure and normalization.

Tip Number Two: Never design a database for the needs of today. Think instead of what possible uses could it be put to years into the future and design accordingly

Tip Number Three: Any element of data that can be broken down into smaller logical parts should be. A name, for example, has a minimum of five fields, Salute, FirstName, MI, LastName, Suffix. Example: Dr. Lester E. Thompson, Ph.D.

Tip Number Four: Put nothing but pristine data in a database. No fancy fonts, colors, attributes etc. Just basic raw text. The fancy stuff will be added in merge files or reports later.

Tip Number Five: Has to do with data entry more than design but it is appropriate because of the capabilities of today’s software. Enter data exactly the way it will be used later. For example; never use all capital letters unless a specific application requires it.

Tip Number Six: All records must have the same fields whether there is data in the field or not. For example, if one person in twelve thousand records requires a field to contain the suffix Jr. then all twelve thousand people must have that field too..

Tip Number Seven: NEVER make any changes to the structure of a database or table without first making a backup copy and storing it in a very safe remote location. You should create a backup copy and keep it updated at all times. Be mindful of how data is stored on a disk and consider carefully before modifying the structure of a table by adding, deleting or moving fields around. Such an action puts the data at great risk while the process is being executed.

Tip Number Eight: It is almost always a good idea to include at least one general purpose Boolean field in a table at conception. While you may not see a need for it today, we never know what tomorrow will bring. A Boolean field is often referred to as a “Flag” or a “Switch” and is exceptionally useful when marking records. For example, you need to keep track of who in the table has sent an RSVP to an invitation of some sort.

Tip Number Nine: Watch the data as it grows. If you see fields that are empty consistently in a table, it is a red flag that says you probably should consider breaking this table down into more than one table and linking them on a common field. This process is referred to as “Data Normalization” and yields a more accurate and efficient database.

Tip Number Ten: Develop a list of field names that you use in every database you create. This will make merging data and tables much easier should it become necessary in the future. Keep them simple, short and meaningful. In different tables of a database keep common field names the same. Example: CustomerID and CutomerNo is not a good idea..

Notes: