MS-Access and Mail Merge-2
A Form Letter can be anything like an Invitation to the Members of a Club or a Notice to the Share Holders of the Company or a Circular to the Head of the Departments of the Organization or a forwarding memo of Departmental Telephone Expenses Statement and so on. In all these cases the body text of the letter will be standard but the addressee information will change on every page of the document.
When we plan for a Form Letter in Microsoft Access we must carefully organize five different elements of this task.
- Design a Table and add a single record to hold the main letter contents like Memo Reference, Date, Subject, Body Text and Letter Footer Information like Department Head who will sign the document etc.
- Design a Form for the above Table for editing the Letter Body Text, for changing contents as and when it becomes necessary.
- Processing of data and designing a sub-report (like a statement on department-wise telephone expenses) if needed to insert into the body of the main letter. If this is not required then this step can be omitted.
- To select the required fields for the document contents, join the Address Book Table with the Letter table (created in step-1).
- Design the Main Form Letter.
A sample image of the completed Form Letter, that we are going to prepare, is given below. Take a look at it so that you will have a general idea, as how we are progressing through the steps to create it:
We need two sample tables, Employees and Orders from the Northwind.mdb Database. (Refer my earlier post Saving Data on Forms not in Table for the exact location reference of the sample file). Import the above Tables from Northwind.mdb database.
If you check the above letter the Addressee information is picked from the Employees Table and the statement appearing in the middle is prepared from the Orders Table. In both tables EmployeeID is a common field and used here to link the embedded statement with the EmployeeID of the Employees Table, so that the correct statement related to the Employee will appear in the letter. All other information; Memo Reference, Date, Subject, letter body text and others; are inserted from the Letter Table that I have mentioned above.
- Design a Table with the following Field Structure and save it with the name Letter:
- Open the Table in datasheet view and enter HRD/ABCD/001 in the FRefNo Field to introduce a record in the Table and close it.
- Click on the Letter Table and select Form from Insert Menu and select Design View from the displayed list.
- Design the Form like the sample image given below.
Write a Macro to close the form Letter. Select the Close Button, display the property sheet, change the Name Property to cmdClose and set the macro to the On Click Event of the Close Button. Click on the Preview button, display the Property sheet and change the Name Property to cmdPreview. We will write a small Sub-Routine later for the Preview button. Display the Property Sheet of the Subject Text Box and set the Scroll Bars Property= Vertical. Make the same change for Para1 and Para2 Text Boxes as well. Save the Form with the name Letter.
- Letter Image given below with the sample data filled-in:
- We will make use of three columns of data, EmployeeID, OrderDate and Freight, from Orders Table to prepare a statement to insert into the Form Letter as shown in the first image above. We will pretend the Freight Value is Sales Figure of each employee and will extract the Year from the OrderDate Field taking it as Sales Year. We need three Queries to prepare a Report Table. Copy and paste the SQL String given below one by one into the SQL Window of new Queries and save it with the Query Names as indicated:
- Query Name: Yearwise_FreightQ0
SELECT Orders.EmployeeID, Val(Format([OrderDate],"yyyy")) AS [Year], Sum(Orders.Freight) AS Sales FROM Orders GROUP BY Orders.EmployeeID, Val(Format([OrderDate],"yyyy"));
- Query Name: Yearwise_FreightQ1
TRANSFORM Sum(Yearwise_FreightQ0.Sales) AS SumOfSales SELECT Yearwise_FreightQ0.EmployeeID FROM Yearwise_FreightQ0 GROUP BY Yearwise_FreightQ0.EmployeeID PIVOT Yearwise_FreightQ0.Year;
- Query Name: Yearwise_IncentiveQ
SELECT YearWise_FreightQ1.* INTO Yearwise_Incentive FROM YearWise_FreightQ1;
NB: You must be careful how you specify the Target Table name for a Make-Table Query and the name of the Query itself. Both should not be same, otherwise you will run into trouble when you run the Query. I have added the letter Q at the end of the Query name Yearwise_IncentiveQ, you can use any name you prefer.
With the first Query we are selecting the data from Orders Table and creates year-wise summary of Freight Value and changing its name to Sales. Second one is a Crosstab Query which transposes the data, year-wise. Since, we cannot directly use Crosstab Query for Report we are creating a table Yearwise_Incentive with a Make-table query.
- Query Name: Yearwise_FreightQ0
- After creating and saving the third Query, double-click on it to run and create the output table Yearwise_Incentive (Click OK to warning messages).
- Design a Report using the Yearwise_Incentive Table as shown below. The size of the Report is approximately 6.5" wide. Do not use Page Header/Footer Sections of the Report. Put the Field Heading Labels on the Report Header Section. Save the Report with the name Yearwise_Incntv.
- We need one more Query to join both Employees Table and the Letter Table that we have created at the beginning. Copy and Paste the following SQL string into the SQL Window of a new Query and save it with the name LetterQ.
SELECT Letter.*, Employees.EmployeeID, Employees.TitleOfCourtesy, Employees.FirstName, Employees.LastName, Employees.Title, Employees.Address, Employees.City, Employees.Region, Employees.PostalCode, Employees.Country FROM Employees, Letter;
- After saving the Query open it in design view and check how both tables are placed on the Query design surface. Remember, we have created only one record in the Letter Table but there are 9 records in the Employees Table. On the Query design area we have placed both queries without joining them together with a common field and placed fields from both tables on the columns. When you open the Query in Datasheet view you can see the Letter Table’s single record is repeating for each record in the Employees table.
NB: If there are more records in the Letter Table then those records will repeat for each record in the Employees Table, which will result in printing more than one letter for each employee.
- Design the Main letter using the LetterQ Query as shown below.
Look carefully at the image. The Report Header/Footer Sections as well as Page Header/Footer Sections of the Report are not used on the Report; instead the header part of the Memo is designed within the FirstName Header Section. To create the FirstName Header/Footer Sections, Click on the Sorting and Grouping Toolbar Button or select the same option from View Menu. On the Displayed Control select FirstName in the Field/Expression Column and in the Group Properties below select Yes in the Group Header and Group Footer properties. When the FirstName Header Section appears, point the mouse over the Detail Section horizontal bar to change the mouse into a cross, click and drag the detail section down to get enough space to design our Letter header Section.
The design is complete when we finish the following the few more steps:
- Insert Yearwise_incntv statement that we have designed earlier between Para1 and Para2 above and link the statement with the EmployeeID field that we have placed separately at the FirstName Header Section. To insert the Yearwise_incntv statement into the main report move the main report to the right of the Database Window and position it side-by-side (click on the Report Tab, if it is not visible) click and drag the statement and place it between Para1 and Para2 and properly place it as shown above. Re-size the report width, if it got expanded to the right.
- Click on the Yearwise_incntv statement, display the Property sheet and insert EmployeeID in Link Child Field and Link Master Field properties.
- Display the property sheet of the EmployeeID control in the Header section and set its Visible Property = False.
- Display the Property Sheet of the Subject control and set the Can Grow, Can Shrink properties to Yes.
- Change the Can Grow, Can Shrink properties of Para1 and Para2 also to Yes.
- Click on the FirstName Footer, display the property sheet and change the Force New Page property value to After Section.
- Save the Report with the name Letter.
- Open the Report in Print Preview, advance the Pages by clicking on the Page Control at the bottom. Check whether the Employee’s name appearing at the Address section at the top, changes on each page and the name appearing in the statement in the middle of the paragraphs is also same as above. Close the Report.
- Open the Letter Form in design view.
- Click on the Preview button and display the Property Sheet. Select [Event Procedure] on the On Click Event property and write the following code in the Form Module and save the Report:
Private Sub cmdPreview_Click() Me.Refresh DoCmd.OpenReport "Letter", acViewPreview End Sub
- Open the Letter Form in normal view. Make some changes in the body text. Click on the Preview button. The changes that you have made in the text should reflect in the previewed letter also.
Your Report display should match with the sample image given at the top of this page.
With this article we have set the stage for Merging of Data Field Values and Built-in Functions into the body text of the Letter and Print it, which we will try next.