Personalize Word documents with the Mail Merge toolbar

Word's Mail Merge feature isn't just for creating form letters and address labels; you can use it to quickly personalize virtually any document--no matter how many copies you need to send or print.

For example, suppose you are compiling a set of handouts for a seminar and would like to add a page that welcomes each participant by name to the event. You have created a list of the participants in an Excel file (called Seminar Attendees) with the following column field headers: Attendee_Name, Telephone_No., and E-mail_Address. Follow these steps:

1. Click in your Word document where you want the attendee's name to appear on the first page of the handouts.
2. Right-click the toolbar area and select Mail Merge to display the Mail Merge toolbar.
3. In the Mail Merge toolbar, select the Open Data Source button.
4. Navigate to the Seminar Attendees file and click Open.
5. Select the name of the worksheet that contains the data.
6. Select the First Row Of Data Contains Column Headers check box, and click OK.
7. Click the Insert Merge Fields button in the Mail Merge toolbar.
8. Click Attendee_Name, Insert, and then Close.
9. Click the Merge To Printer button.

Word will print a personalized set of handouts for each attendee.

If you learn that two people on the list cancelled, there is no need to have Word print their handouts. Before printing, click the Mail Merge Recipients button in the Mail Merge toolbar, and clear the check mark from the records of the people who will not attend the seminar.

Microsoft Excel

If you add a row or two of data to your spreadsheet, you can easily update the Excel chart pertaining to that data by changing the chart's data range. However, if you find yourself continually changing the data range, you may want to set up the chart so that it automatically updates as new data is added.

Suppose you are keeping track of the maximum temperature for each day in April. You have entered the first two dates for April in Column A, which contains the field name Date in A1. The corresponding temperature for each day will appear in Column B, which contains the field name Temperature in B1. Thus, you enter 55 in B2 for 4/1/06 in A2, and 60 in B3 for 4/2/06 in A3. You select A1:B3, and create a chart for Maximum Daily Temperatures for April. To have the chart update automatically as each new row is added daily, follow these steps:

1. Go to Insert | Name| Define.
2. Enter Date in the Names In Workbook text box.
3. Enter the following formula in the Refers to text box:
   

   =OFFSET(Sheet1!$A$2,0,0,COUNTA
   (Sheet1!$A:$A)-1)

4. Click Add.
5. Enter Temperature in the Names In Workbook text box.
6. Enter the following formula in the Refers To text box:
   

   =OFFSET(Sheet1!$B$2,0,0,COUNTA
   (Sheet1!$B:$B)-1)

7. Click Add and then OK.
8. Click on the chart, and then on the data series.
9. Change the formula in the formula bar to the following:
   

   =SERIES(,Sheet1!Date,Sheet1!Temperature,1)

The chart will update automatically each day with a new temperature. Be sure you don't use Columns A and B for any other data; otherwise, COUNTA will return an incorrect value.

Microsoft Access

IT managers often want to know data points about a particular quarter. If you need to retrieve this information, you can use a Between expression in a query's Criteria field to find all Access records between a range of dates, such as Between #4/1/2005# and #6/30/2005#. Another alternative is to use the DatePart expression, which lets you find all records for a quarter.

Let's say you need to obtain a listing of all employees who were hired the second quarter of 2005. To obtain the answer using the DatePart function, follow these steps:

1. Open the Employees database and click Queries under Objects in the Database Windows.
2. Click New and then OK.
3. Select the Employees table from the list, and then click Add.
4. Double-click the Employee ID, First Name, and Last Name fields from the Employees table field list.
5. Click in the next blank field cell and enter the following expression:
   Quarter: DatePart("q",[Hire Date])
6. Enter 2 in the Quarter field's criteria cell.
7. Click in the next blank field cell and enter the following expression:
   Year: Year([Hire Date])
8. Enter 2005 in the Year field's Criteria cell. Then, simply save and run your query.

You can also use the DatePart and Year expressions in a parameter query and allow users to enter the quarter and year themselves.