Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    2

    Unanswered: Household address sort in Paradox 9

    I couldn't find a reference for this in any of my manuals. Do you know of a reference source that would discuss the following query and present a solution? The US postal service can do this with their Accuzip program, but I would like to run it myself in Paradox.

    Poblem:

    Current database contains multiple contacts at same mailing address. All persons in household receive a copy of snail mail distibutions. Postage and printing costs are high.

    Objectives of Query:

    (1) reduce # of mail pieces sent (reduce postage and printing costs)
    (2) Combine multiple contacts at the same address into Single Contact
    (3) Create a new database from existing database comprised of (a) all customers that have a single contact, and (b) based on an address match--create a single customer (with all contacts listed in Name field).

    For example: allcustomer.db

    Field 1: Customer # 10001 (Primary Key)
    Field 2: Name Bill Boing
    Field 3: Address 2 Main
    Field 4: City Somewhere
    Field 5: State OR
    Field 6: Zip 97208

    Field 1: Customer # 10011 (Primary Key)
    Field 2: Name Larry Mailer
    Field 3: Address 100 Second Street
    Field 4: City Somewhere Else
    Field 5: State OR
    Field 6: Zip 97209

    Field 1: Customer # 19992 (Primary Key)
    Field 2: Name Sally Houser
    Field 3: Address 2 Main
    Field 4: City Somewhere
    Field 5: State OR
    Field 6: Zip 97208

    Result required: mailcustomer.db

    (Note Sally Houser name has been added to Bill Boing's Name field and her seperate customer information is not repored in the solution)

    Field 1: Customer # 10001-M (Primary Key)
    Field 2: Name Bill Boing or Sally Houser
    Field 3: Address 2 Main
    Field 4: City Somewhere
    Field 5: State OR
    Field 6: Zip 97208

    Field 1: Customer # 10011 -M (Primary Key)
    Field 2: Name Larry Mailer
    Field 3: Address 100 Second Street
    Field 4: City Somewhere Else
    Field 5: State OR
    Field 6: Zip 97209

    Any assistance you can provide will be appreciated.
    fm

  2. #2
    Join Date
    Oct 2003
    Posts
    107
    If it was me, I'd do a scan loop instead of a query.

    First you have to make the name field a bit larger to handle the added name(s).

    Then query the allAddress table to produce an unkeyed copy of it (answer.db). Sort answer.db on the address field, then key ID number (obviously you might want to check the city and zip too, but we're doing a simple version).

    Now you have a sorted table, ordered to have duplicate addresses grouped together with the (normally) keyed field descending. Now you need to identify duplicates and update the real table.

    For this you simply open both tables with tCursors, and do a scan of the sorted answer table. When an address matches that of a previous record (use a variable placeholder), locate the previous record's key value in the real table and modify that record's name field to add "or so-and-so". Blank the name field on the duplicate record of the answer table (so you can do a delete query in a moment). Once the scan is complete, the real table will still have duplicate address records, but the primary record for that address with include all the names. Then do a joined delete query, linking the real table (as DELETE) and answer table on the key field and using the reserved word BLANK in the name field of the answer table.

    All the records you blanked the address field on in the answer table will be removed from the real table, leaving you with only single records for each address.

    If you need help with the code let me know.
    Last edited by lmckelvy; 02-20-04 at 12:13.

  3. #3
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    2
    lmckelvy

    Thanks for your assistance.

    fm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •