Consolidating a Mailing List

    A mailing list may contain more than one person's name at some
addresses.  If you want to mail only one copy of an item to each
address, the following procedure will edit a Q&A database so that one 
record from each address will be marked for inclusion in a mailing 
list.  In addition, that record will include an appropriate name for 
the household.  The name used will be the single person's name, the 
family name if all the names on the list have the same surname, or 
"Resident", if people with differing surnames share the same address.

    You will need to add several fields to your database.  These fields are:

    Name                Format    Purpose

    Record Number:      Number    index records for lookup functions

    Household:          Text      replaces First and Last name on
                                  labels

    Mail to?:           Yes/No    flag one copy of each address for
                                  inclusion on mailing list

    Extra Field:        Text      holds family/group markers and makes
                                  it easy to see programming
                                  statements.

    After adding the fields to your database, go to File, Design, Customize,    
    Speed-up and put an "S" in the Record Number field.

    Actually marking and updating the records in your mailing list
will take two passes through Mass Update.  The first time, sort the file by 
address in ascending order.  (F8 from the Retrieve Spec to get the Sort Spec 
screen.)  On the Update Spec, enter the following:

    First Name: #1    Last Name: #3    Address: #5
    Record Number: #7 = @NUMBER        Household: #9       Mail to?:
    Extra Field: #11:
     IF #5 = @XLOOKUP(@FILENAME, #7-1, "Record Number", "Address")
     THEN {
      IF #3 = @XLOOKUP (@FILENAME, #7-1, "Record Number", "Last Name")
      THEN {
        IF @XLOOKUP(@FILENAME, #7-1, "Record Number", "Household") =
        "Resident"
        THEN #9 = "Resident"
        ELSE #9 = "The" + #3 + "Family"}
      ELSE #9 = "Resident"}
     ELSE #9 = #1 + " " + #3


    This programming tells Q&A to look for matching addresses.  If the last 
record it looked at has the same address as the current record, Q&A will 
compare last names as well and enter the family name or "Resident" on the 
household line.



Q & A Application Note       Rev. 6/92           #2110     Page 1 of 2


                     Consolidating a Mailing List

    The second time through Mass Update, no sort will be needed.  
Enter the following on the Update spec.

    First Name:       Last Name:       Address:
    Record Number: #7                  Household:     Mail to?: #10
    Extra Field: #11: #10 = "Y";
      IF @INSTR(@XLOOKUP(@FILENAME, #7+1, "Record Number", "Extra
         Field"),"Family") >0
      THEN #10 = "N";
      IF @XLOOKUP(@FILENAME, #7+1, "Record Number", "Extra Field") =
         "Resident"
         THEN #10 = "N"


    This time Q&A looks ahead.  If the next record is marked as either 
"Family" or "Resident", the current record has the same address and 
should not be included in the mailing.


    When preparing mailing labels from the database, simply retrieve 
only labels with "Y" in the "Mail to?" field.


































Q & A Application Note       Rev. 6/92           #2110     Page 2 of 2