Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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

4. 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 titleif 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

5. 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])

6. 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
addresswe 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.