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

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.

lmckelvy

Thanks for your assistance.

fm