When you have an address list with two people at the same address, it is
often preferable to print a single mailing label for that address that
lists both people's names. To accomplish this, you first need to create a
query that contains the first name to print on the label, and then create a
query that contains the second name for the label. Then you can put these
two queries together to show both names for each address in a third query.
For this method to work, it is important that
all of the address information for each person at the same address be identical. For example,
123 First Street N
-and-
123 First St. North
are the same address, but not identical.
The following example uses the aggregate functions
First() and
Last() to create lists of primary label names and secondary label names, respectively.
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
- Start Microsoft Access and open the sample database Northwind.mdb.
- In the Database dialog box, under Objects, click Tables.
- Open the Employees table in Datasheet view, and then add the following three records to create duplicate addresses. Note that some fields in each record are left blank. Carefully type the information exactly as it appears below:
Record #1 Record #2 Record #3
-------------------------------------------------------------------
Last Name: Fuller Wilkinson Smith
First Name: Mary Avery John
Birth Date: 7/2/54 4/30/77 10/20/58
Address: 908 W. Capital Way 14 Garrett Hill 4110 Old Redmond
Rd.
City: Tacoma London Minneapolis
Region: WA MN
Postal Code: 98401 SW1 8JR 55435
Country: USA UK USA
- Create a new query in Design view based on the Employees table. This query lists the first set of names for your mailing labels.
- On the View menu, click Totals. Then complete the design of your query as follows, and save it as qryLabelNames1:
Query: qryLabelNames1
------------------------------------------------------
Type: Totals Query
Field: ListName: First([FirstName] & " " & [LastName])
Table: Employees
Total: Expression
Sort: Ascending
Field: Address
Table: Employees
Total: Group By
Sort: None
Field: City
Table: Employees
Total: Group By
Sort: None
Field: Region
Table: Employees
Total: Group By
Sort: None
Field: PostalCode
Table: Employees
Total: Group By
Sort: None
Field: Country
Table: Employees
Total: Group By
Sort: None
- Select the qryLabelNames1 query in the Database list box, and then on the Edit menu, click Copy.
- On the Edit menu, click Paste. In the Paste As dialog box, type qryLabelNames2 in the Query Name box, and then click OK.
- Open the qryLabelNames2 query in Design view, and then modify only the ListName field so that it uses the Last() function instead of the First() function:
Field: ListName: Last([FirstName] & " " & [LastName])
Table: Employees
Total: Expression
Sort: Ascending
- Save the query and close it. This query lists the second set of names for your mailing labels.
- Create a new query in Design view based on the qryLabelNames1 and qryLabelNames2 queries. This query is the basis for your mailing label report.
NOTE: The expression in the Name2 field contains an underscore (_) at the end of the line as a line-continuation character. Remove the underscore and type the entire expression as a single line when you re-create this example.
Query: qryMailingList
---------------------------------------------------------
Type: Select Query
Join: qryLabelNames1.Address <-> qryLabelNames2.Address
Join: qryLabelNames1.PostalCode <-> qryLabelNames2.PostalCode
Field: Name1: ListName
Table: qryLabelNames1
Sort: None
Field: Name2: IIf([qryLabelNames1].[ListName]= _
[qryLabelNames2].[ListName],"",[qryLabelNames2].[ListName])
Sort: None
Field: Address
Table: qryLabelNames1
Sort: None
Field: City
Table: qryLabelNames1
Sort: None
Field: Region
Table: qryLabelNames1
Sort: None
Field: PostalCode
Table: qryLabelNames1
Sort: None
Field: Country
Table: qryLabelNames1
Sort: None
When you run this query, note the following:
- Each record displays a name in the Name1 column, but where two or more people live at the same address, a name also appears in the Name2 column.
- Even though two records exist with an Address field of "4110 Old Redmond Rd.," they are printed on separate labels because their postal codes are different.
This result occurs because you joined both the Address and the PostalCode fields in the qryMailingList query.
Now you can use the Label Wizard to create labels based on the qryMailingList query.