Microsoft KB Archive/169153

From BetaArchive Wiki
Knowledge Base


ACC: Sample Query to Print One Label for Two People in a List

Article ID: 169153

Article Last Modified on 1/19/2007



APPLIES TO

  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q169153

Moderate: Requires basic macro, coding, and interoperability skills.


SUMMARY

When you have a list of names for which you would like to generate mailing labels, there are frequently two people listed at the same address. This article demonstrates a technique to create a query from which to print a single mailing label for two people at the same address.

NOTE: If more than two people on your mailing list live at the same address, only the names from the first and last record with that address in your table will appear in the query.

MORE INFORMATION

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 is identical. For example,

   123 First Street N
                


is different from

   123 First St. North
                


The following example uses the aggregate functions First() and Last() to create lists of primary label names and secondary label names, respectively.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.

  1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
  2. Open the Employees table in Datasheet view, and add the following three records to create duplicate addresses. Note that some fields in each record will be 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
                            
  3. Create a new query in Design view based on the Employees table. This query will list the first set of names for your mailing labels.
  4. On the View menu, click Totals. Then complete the design of your query as follows and save it as qryLabelNames1.

    NOTE: In Microsoft Access 2.0 the fields [Last Name], [First Name] and [Postal Code] contain a space in their names.

          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
                            
  5. Select the qryLabelNames1 query in the Database window, and then on the Edit menu, click Copy.
  6. On the Edit menu, click Paste. In the Paste As dialog box, type qryLabelNames2 in the Query Name box, and then click OK.
  7. Open the qryLabelNames2 query in Design view, and only modify the ListName field so it uses the Last() function instead of the First() function:

          Field: ListName: Last([FirstName] & " " & [LastName])
             Table: Employees
             Total: Expression
             Sort: Ascending
                            
  8. Save the query and close it. This query will list the second set of names for your mailing labels.
  9. Create a new query in Design view based on the qryLabelNames1 and qryLabelNames2 queries. This query will be the basis for your mailing label report.

    NOTE: The expression in the field called Name2 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 recreate 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 will print on separate labels because their postal codes are different. This happens because you joined both the Address and the PostalCode fields in the qryMailingList query.

Now you can use the Label Wizard (or Mailing Label Wizard in version 2.0) to create labels based on the qryMailingList query.

REFERENCES

For more information about using the Label Wizard, search the Help Index for "mailing labels," or ask the Office Assistant.

Keywords: kbhowto kbprint KB169153