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梚f 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-04.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梬e 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.
No comments:
Post a Comment