|[ Team LiB ]|
Recipe 1.5 Group Mailing Labels by Address
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.
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 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.
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]
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
|[ Team LiB ]|