Support 805.461.7300
Sales
NEW SITE. Our Technology. Your Success.

Combine multiple records into one record that have the same last name at the same address

Example Input:
John Smith 100 Main St
Jane Smith 100 Main St
Kevin Smith 100 Main St
Result you will see in the field named FIRST:
The Smith Family

INSTRUCTIONS
Make sure the name information is separated into the First and Last fields. if not you can Parse the name information from the Special>Parse> Name option.
Run the duplicate search from the Select>Show Duplicates option, with Household selected in the general tab as the search criteria.
IMPORTANT: Make sure to select the "Backup Duplicates option" at the bottom of the screen.
Update the database with the duplicate results.
Run the following command from the Command window (Edit>Command) You can copy/paste the following command into the Command statement Syntax area of the command window and click RUN.

ON ERROR com_message=message() 
PUBLIC cDupFilePath,comAlias,comUnique 
PUBLIC ARRAY comArFiles(1,1) 
SET SAFETY OFF 
* Get the current Databases Alias Name 
comAlias=ALIAS() 
* Capture all back file names in memory 
=ADIR(comArFiles,ADDBS(JUSTPATH(DBF(comAlias)))+"*.rpt") 
* Create the variable name for the duplicate records 
cDupFilePath=ADDBS(JUSTPATH(DBF(comAlias)))+FORCEEXT(comArFiles(1,1),'dbf') 
comUnique=SYS(2015) 
USE (cDupFilePath) IN 0 ALIAS (comUnique) 
SELECT(comUnique) 
INDEX ON therec_xx tag therec_xx 
SELECT(comAlias) 
ALTER TABLE (comAlias) ADD COLUMN priority C(1) 
ALTER TABLE (comAlias) ADD COLUMN name2 C(60) 
replace name2 with first for "1"$(priority)
replace all priority with "" 
REPLACE priority WITH IIF(!DELETED(),'1','2') for SEEK(RECNO(),comUnique) 
RECALL ALL 
replace first with "The "+allt(last)+" Family", last with "" for "1"$(priority)
delete for "2"$(priority)
pack
USE IN IIF(USED(comUnique),SELECT(comUnique),0) 
RENAME (FORCEEXT(cDupFilePath,"rpt")) ; 
   TO (cDupFilePath+".txt")

« Back to Commands

AccuZIP’s Professional Services Team has been a great resource for helping me launch Informed Delivery campaigns for my clients. I am a small direct mail marketing agency specializing in helping my clients find and retain new customers with direct mail. Informed Delivery is the best thing the post office has come up with in my 30+ years in the business. However, just like everything else with the post office, they don't make it easy. AccuZIP has made doing these digital campaigns very simple. I have analyzed campaign results and found the price per click through to the client's website less than an Adword campaign. I am looking forward to working with AccuZIP and their entire Professional Services Team with more clients and campaigns.
S.R., Raleigh, NC