Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 1.5 Group Mailing Labels by Address

1.5.1 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.

1.5.2 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:

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.
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, with new column aliases

Add a column to the query grid in which Access will count the number of rows that it groups together to make a single row in the output. Choose any field that won't have null values (i.e., a required field), place it in the query grid, and set its Total row to Count. (This field is called [Residents] in this example.) This instructs Access to count the number of rows in the same grouping, as shown in Figure 1-10. You can also use the expression Count(*) instead of using a field.
Add any other fields that you want to show on your labels to the query grid. For each field, set the value in the Total row to First. For each column, add a specific title—if you don't, Access will change each title to FirstOf<ColumnName>. When you run this query, its output will look something like that shown in Figure 1-11. Note that there's only one row in the output for each unique grouping of last name, address, and zip code.

Figure 1-11. The output of the grouping query qryCountNames

To create the text for your labels, create a new query (qryLabels, in this example) based on the previous query (qryCountNames). You'll base the mailing label name on the field in which you counted rows ([Residents], in this example), along with the [FirstName] and [LastName] fields. Pull in whatever columns you want in your label, and add one more for the label name. In our example, the expression for this column ([LabelName]) is:
```
LabelName: Iif ([Residents] > 1, "The " & [LastName] & " Family",
 [FirstName] & " " & [LastName])
```
On the mailing label itself, use the [LabelName] field instead of the [FirstName] and [LastName] fields. This field (shown in Figure 1-12) shows either the family name or the single individual's first and last name, depending on the value in the [Residents] column.

Figure 1-12. The LabelName field showing the family name or the individual's name

To see how this works, open the tblNames table in 01-05.MDB. The raw data appears as in Figure 1-13. Note that there are several examples of family members living at the same address, and we want to create only one label for each of these families. There's also an example of two people with different last names at the same address—we don't want to combine these names into one label. Open the rptLabels report (shown in Figure 1-14). This mailing label report groups the people with common last names and addresses onto single labels, using the family name instead of individual names.

Figure 1-13. Sample data from tblNames that includes multiple people per address

Figure 1-14. Mailing labels, grouped by last name, address, and zip code

1.5.3 Discussion

By creating a totals query that groups on a combination of fields, you're instructing Access to output a single row for each group of rows that have identical values in those columns. Because you're grouping on last name and address (the zip code was thrown in to ensure that you wouldn't group two families with the same name at the same address in different cities), you should end up with one output row for each household. You included one column for counting (the [Residents] field, in our example), so Access will tell you how many rows collapsed down into the single output row. This way, the query can decide whether to print an individual's name or the family name on the label.

If the value in the counted field is greater than 1, the query builds an expression that includes just the family name:

"The " & [LastName] & " Family"

If the count is exactly 1, the query uses the first and last names:

[FirstName] & " " & [LastName]

The immediate If function, IIf, does this for you, as shown in Step 5. It looks at the value in the [Residents] field and decides which format to use based on that value.

Access does its best to optimize nested queries, so don't feel shy about resorting to basing one query on another. In this case, it simplifies the work. The first-level query groups the rows, and the second one creates the calculated expression based on the first. Though it might be possible to accomplish this task in a single query, splitting the tasks makes it easier to conceptualize.

We also could have solved this problem by changing the design of the database so that instead of having a single table, tblNames, with repeating address information for multiple family members, we had two tables, perhaps called tblFamilies and tblFamilyMembers, related in a one-to-many relationship.

1.5.4 See Also

To include quotes inside quoted strings, see Recipe 7.1 in Chapter 7.

[ Team LiB ]