Duplicate programming question

[PremiumL]

I have 2 databases.

1. Master.dbf
2. New.dbf

Both databases have the same layout. I need a program that will take the address field (called address) and zip code field (called zip) and see if it matches the records in the master.dbf. If there are duplicates, I want to have them deleted out of the new.dbf. Master.dbf should remain untouched.

Thanks very much for your help,

Jarrett Chanzes

 

[bobmpalmer]

I'm sure others will have a simpler solution but off the top of my head:-

**Create a temp cursor
CREATE cursor mytemp (address C (100),ZIP C (30))
USE master in 0
USE new in 0
SELECT new
SCAN
    newdeleted=.f.
    SELECT mytemp  && Check if we already got it
    SCAN FOR ALLTRIM(UPPER(Mytemp.address))=ALLTRIM(UPPER(NEW.address)) .and. ALLTRIM(UPPER(Mytemp.zip))=ALLTRIM(UPPER(NEW.zip))
        IF FOUND()
           *** Its in the temp table so it must aleady exist in NEW
           SELECT NEW
           DELETE
           newdeleted=.t.
         ENDIF
    ENDSCAN
    IF !newdeleted  && Its not in the temp

    SELECT MASTER
        SCAN FOR ALLTRIM(UPPER(MASTER.address))=ALLTRIM(UPPER(NEW.address)) .and. ALLTRIM(UPPER(MASTER.zip))=ALLTRIM(UPPER(NEW.zip))
           If FOUND() && Its in the master so add it to temp
              SELECT mytemp
              APPEND BLANK
              replace mytemp.address WITH NEW.address,;
                      mytemp.zip     WITH NEW.zip
           ENDIF
        ENDSCAN
    ENDIF
ENDSCAN

As I say someone will come up with another way no doubt. Bear in mind this will not deal with records in NEW that dont exist in master



Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[fitedgar]

select * ;
From New.dbf ;
where address + zip NOT IN ;
(SELECT address+zip FROM master.dbf) ;
INTO CURSOR AC_DATA
This will Create a sample file where if you wish can overwrite the new.dbf
Ex.
USE IN new
SELECT ac_data
copy to new.dbf

ETC...
Good Luck
EMC

 

[1oldfoxman]

This presumes that the duplicates are EXACT matches:

use master in 0
index on address+zip to masterindex && or use exising index
use new in 0
set relation to address+zip into master
delete for address = master.address .and. zip = master.zip
pack



Otherwise you can use Bob's Alltrim(Upper()) structure here as well.

HTH,
Dennis