Access Cookbook

Group Mailing Labels by Address

Problem

You need to print mailing labels intended for the general public. If your mailing list contains multiple occurrences of the same last name at the same address, you want to print only one label (addressed to the entire family). Otherwise, you need to print one label for each person in the table.

Solution

To avoid sending duplicate mailings to multiple members of a family, you can use a totals query to group label data so that people with the same last name who live at the same address will make up only one row in the output query. In addition, if you count the number of occurrences of combinations of last name, address, and zip code, you can create the mailing-label text with different text for mailings to a family based on that count.

To create this grouping in your own data, follow these steps:

1. Create a new query (qryCountNames, in this example) based on your table. Turn this query into a totals query by choosing View → Totals or by clicking on the Sigma button on the toolbar. This query will group the data using one row for each unique combination of the grouping fields.

2. Add a column to the query grid for each column in your table on which you want to group rows. Our example uses [LastName], [Address], and [Zip]. For each column, set the Total field to Group By. If you want to specify column names, place those names, followed by a colon, before the field names, as shown in Figure 1-10.

   Figure 1-10. The grouping query, qryCountNames, ...