MS-Access and Mail Merge-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Form Letters

A form letter can serve various purposes, such as an invitation to club members, a notice to company shareholders, a circular to department heads, or a forwarding memo for departmental telephone expense statements. In all these cases, the body of the letter remains the same, while the recipient’s details vary from page to page.

When planning a form letter in Microsoft Access, it's essential to organize the following five elements:

Design a Letter Table.
Create a table to hold the main content of the letter. This table should include fields such as Memo Reference, Date, Subject, Body Text, Footer information (e.g., Department Head's name), and other relevant details. Enter a single record into this table to serve as the letter's template.
Create a Letter Editing Form.
Design a form based on the Letter Table to allow easy editing of the body text and other details. This makes updating the letter content straightforward whenever changes are needed.
Prepare a Sub-Report (if applicable).
If the letter requires inserting dynamic content, like a department-wise telephone expenses statement, design and process the necessary sub-report. If no such statement is needed, this step can be skipped.
Join Letter and Address Data.
Use a query to join the Letter Table with the Address Book or recipient list. This will allow you to merge personalized address data with the letter content.
Design the Main Form Letter Report.
Finally, design the report that serves as the form letter. Insert the editable letter fields, recipient address fields, and any sub-reports (if used). Format the layout to suit your printing requirements.

A sample image of the completed form letter we're aiming to create is shown below. Take a moment to review it—it will give you a clear understanding of the final result and how each step in the process contributes to building the letter.

Click to Enlarge

The Design Task

We need two sample tables, Employees and Orders, from the Northwind.mdb Database. (Refer to my earlier post 'Saving Data on Forms not in Table' for the exact location reference to the sample file), Import the above Tables from the Northwind.mdb database.

As you can see in the form letter above, the addressee information is drawn from the Employees table. The statement shown in the middle of the letter is generated from the Orders table. Both tables share a common field, EmployeeID, which is used to link the embedded statement to the corresponding employee. This ensures that the statement displayed in the letter pertains to the correct individual. All other content—including the memo reference, date, subject, body text, and footer details—is sourced from the Letter table mentioned earlier.

Design a Table with the following Field Structure and save it with the name Letter:
Open the Table in the 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 the Insert Menu, and select Design View from the displayed list.
Design a Form, like the sample Form image shown below.

Click to Enlarge

Create a macro to close the form by following these steps:
Select the Close button, open its Property Sheet, change its Name property to 'cmdClose', and assign a macro to its On Click event to close the form.
Next, select the Preview button, display its Property Sheet, and rename it to 'cmdPreview'. We will add a small VBA subroutine for this button later.
For the Subject text box, open its Property Sheet and set the Scroll Bars property to Vertical. Make the same change for the Para1 Para2 text boxes to allow scrolling for longer text.
Finally, save the form with the name Letter.
The Letter Image is given below with the sample data filled in:

Click to Enlarge
Inserting a Statement.
To prepare the embedded statement for the Form Letter (as shown in the earlier image), we will use three columns from the Orders table: EmployeeID, OrderDate, and Freight. For demonstration purposes, we will treat the Freight value as the Sales Figure for each employee and extract the year from the OrderDate field to represent the Sales Year.
We will create three queries to build a Report Table. Follow these steps:
1. Open a new query in SQL View (do not select any table when prompted).
2. Copy and paste the SQL string provided for each query.
3. Save each query with the name indicated after the code block.
This setup will help organize the sales summary data by employee and year for use in the form letter.
- 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;
```
Note: Be careful when naming your Make-Table Query and the Target Table it creates. Do not give them the same name — doing so can cause errors or unexpected behavior when the query is executed.

For example, in the query named Yearwise_IncentiveQ I’ve added a “Q” at the end to distinguish it from the table it creates. You are free to use any naming convention you prefer, as long as the query name and the target table name are not identical.

In the first query, we extract data from the Orders table and create a year-wise summary of the Freight values, renaming it as Sales. The second query is a Crosstab Query, which transposes the data so that the years appear as column headers.

However, since Crosstab Queries cannot be used directly in Reports, we use a Make-Table Query to store the transposed results in a new table named Yearwise_Incentive. This allows us to use the data easily in reports or for further processing.
After you have created and saved the third query, double-click on it to run. This will generate the output table named Yearwise_Incentive. If you receive any warning messages during the process, simply click OK to proceed.
Design a Report using the Yearwise_Incentive Table as the record source, following the layout shown in the sample image (refer to your earlier example). The report width should be approximately 6.5 inches.
- Do not use the Page Header/Footer sections.
- Place all field heading labels in the Report Header section.
- Arrange the detail fields accordingly in the Detail section to align under their respective labels.
- Once completed, save the report with the name: Yearwise_Incntv.
Click to Enlarge
We need to create one final query to join the Employees table with the Letter table (created earlier to store the memo reference, subject, and body text).
To do this:
1. Open a new query in Design View, but do not add any tables yet.
2. Switch to SQL View.
3. Copy and paste the following SQL string into the SQL window:
```
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;
```
4. Save the query with the name: LetterQ.
After saving the query, open it in Design View and examine how the Employees and Letter tables are arranged on the design surface. Note that the Letter table contains only one record, while the Employees table has nine. Since we haven't defined a join between the two tables, Access performs a Cartesian product, repeating the single record from the Letter table for each record in the Employees table. When you open the query in Datasheet View, you'll see the Letter content duplicated for every employee.

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.
Here’s why this happens:
- In your query LetterQ No JOIN condition is specified between the Employees table and the Letter table.
- Because of that, Access performs a Cartesian Product (also called a cross join):
  
  Each record in the Employees table is combined with every record in the Letter table.
- Since Letter contains only one record, the result is that this single letter content is repeated for each employee.
What This Means in Practice:
When you use this query as the Record Source for your form letter report:
- Each page (or section) of the report will show one employee’s information,
  along with the same shared letter content from the Letter table.
- This is exactly what we want when preparing a bulk personalized form letter —
  Each employee gets their copy of the same letter, but with their name, address, and individual details.
✅ Next Steps: You can now use the LetterQ query as the Record Source for your final report — the actual Form Letter Report.
Design the Main letter using the LetterQ query as shown below.
Take a close look at the image. The Report does not use the Report Header/Footer or Page Header/Footer sections. Instead, the memo’s header is designed within the FirstName Header section. To create this section, click the Sorting and Grouping button on the toolbar, or choose Sorting and Grouping from the View menu. In the dialog that appears, select FirstName in the Field/Expression column. Then, in the Group Properties section below, set Group Header and Group Footer to Yes. Once the FirstName Header section appears in the report design, position your mouse over the top edge of the Detail section (the horizontal bar). When the pointer changes to a double-headed arrow, click and drag downward to create enough space for designing the memo's header layout.

Click to Enlarge
The design is complete when we finish the following few more steps:
Inserting the Statement
- To insert the Yearwise_Incntv statement into the main report, place it between Para1 and Para2, and link it to the EmployeeID field that was added separately in the FirstName Header section.
  To do this:
  1. Move the main report slightly to the right side of the screen, next to the Database Window, so both are visible side by side.
  2. If the Yearwise_Incntv report is not visible, click its tab to bring it forward.
  3. Click and drag the Yearwise_Incntv report into the Detail section of the main report, positioning it between the Para1 and Para2 text boxes.
  4. Align and size the inserted report properly to fit the layout as shown in the reference image.
  5. If the main report’s width has expanded to the right during this process, resize it appropriately to maintain the original 6.5" width.
  Make sure the Subreport control is properly linked using EmployeeID as the master and child field so that the correct data appears for each employee.
- Click on the Yearwise_incntv statement, display the Property sheet, and insert EmployeeID in the 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.
Preview of Report
Open the report in Print Preview and use the Page Navigation controls at the bottom to move through the pages. As you advance, verify that the Employee Name displayed in the address section at the top of each page changes appropriately. Also, ensure that the Employee Name shown in the statement inserted between the paragraphs matches the one in the address section. Once you've confirmed that the data is displaying correctly, close the report.
Open the Letter Form in the 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 a normal view. Make some changes in the body text. Click on the Preview button. The changes that you have made in the text should also be reflected in the previewed letter.

Your Report display should match the sample image given at the top of this page.

With this article, we have laid the groundwork for merging data field values and built-in functions into the body text of a letter. In the next step, we will explore how to incorporate these elements and print the final document.