counting zip codes as duplicates when first 5 digits are the same

[johnmerlino]

Hey all,

I have this query:

SELECT * INTO KeepThese
FROM final_output_summary_judgment AS f LEFT JOIN (SELECT t.fullName FROM final_output_summary_judgment AS t GROUP BY t.fullName HAVING Count(t.fullname)>=6)  AS Ex ON f.fullName=Ex.fullName
WHERE Ex.fullname Is Null;

Although it is supposed to remove any duplicates, it doesn't realize that where the 5 digit zip codes are the same that these should count as duplicates, but it doesn't count them as duplicates because they are not the same:

 
Schmidt,James    6800 NW 24 TER    FORT LAUDERDALE    FL    33309    
Schmidt,James    6800 NW 24 TER    FORT LAUDERDALE    FL    33309-1412

Is there a way to get it so that it keeps only one of these records only because the first five digits match of the zip code and all the other fields match. I would only want to preserve the record with the longer zip code in these types of situations.



Thanks for response.

[ajetrumpet]

could you use distinctrow i wonder?

select distinctrow field1, field2, field3, etc, etc, etc..., left([zipcode], 5)

from table (join statements)

where instr([zipcode], "-") = 0

I can't remember, but I don't think distinctrow works with just one table, so that accompanying left join might be fine.

[weekend00]

ajetrumpet 's suggestion will do it, but need to change distinctrow to distinct.

[johnmerlino]

I tried this:

SELECT DISTINCT fullName, addresses, cities, us_states_and_canada, zip_codes INTO final_output
FROM (SELECT possibles.fullname, PrepareForDuplicateCheck.addresses, PrepareForDuplicateCheck.cities, PrepareForDuplicateCheck.us_states_and_canada, PrepareForDuplicateCheck.zip_codes FROM possibles, PrepareForDuplicateCheck WHERE instr(PrepareForDuplicateCheck.zip_codes, "-") = 0 and INSTR(PrepareForDuplicateCheck.names_1, possibles.fullname) > 0   Or   INSTR(PrepareForDuplicateCheck.names_2, possibles.fullname) > 0)  AS [%$##@_Alias];

And it still came back with this:

        fullName addresses cities us_states_and_canada zip_codes
 Serrano,Sergio 6528 NW 1 ST MARGATE FL 33063-5102     
Serrano,Sergio 6528 NW 1 ST MARGATE FL 33063

when all i needed was this:

Serrano,Sergio6528 NW 1 STMARGATEFL33063-5102

Thanks for response.

[weekend00]

Obviously, you didn't following what Aje said.

Please try following query:

SELECT distinct possibles.fullname, PrepareForDuplicateCheck.addresses, PrepareForDuplicateCheck.cities, PrepareForDuplicateCheck.us_states_and_canada, left(PrepareForDuplicateCheck.zip_codes,5) FROM possibles, PrepareForDuplicateCheck WHERE instr(PrepareForDuplicateCheck.zip_codes, "-") = 0 and INSTR(PrepareForDuplicateCheck.names_1, possibles.fullname) > 0 Or INSTR(PrepareForDuplicateCheck.names_2, possibles.fullname) > 0

[johnmerlino]

Actually, I didn't explain myself well.

This query doesn't work:

SELECT DISTINCT uniSelectedContacts.addresses,uniSelectedContacts. cities,uniSelectedContacts.us_states_and_canada,un iSelectedContacts.zip_codes,uniSelectedContacts.Th eName FROM uniSelectedContacts WHERE instr(uniSelectedContacts.zip_codes, "-") = 0

If I have these records:
TheName addresses cities us_states_and_canada zip_codes
Doe,Jane 333 1st ave Coral Florida 33311
Doe,Jane 333 1st ave Coral Florida 33311-1234
Smith,John 111 3st ave Key West Florida 33123
Bacon,Joe 222 2st ave Saturn Florida 11122
Bacon,Joe 222 2st ave Saturn Florida 11122


I would only want it to return:
TheName addresses cities us_states_and_canada zip_codes
Doe,Jane 333 1st ave Coral Florida 33311-1234
Smith,John 111 3st ave Key West Florida 33123
Bacon,Joe 222 2st ave Saturn Florida 11122

The above query is flawed because if zip code doesn't contain "-", the record will not be returned. The reason why "-" is in the query is because IF there are two records where the TheName, addresses, cities, us_states_and_canada are all identicial, but zip is identical except for one with 33311 and one with 33311-1234, then I only want to preserve record with 33311-1234. However, in all other cases, where there is no duplicate of TheName, addresses,cities,us_states_and_canada, and zip_codes (the first five digits, that is), then I do want to preserve the record with first five digits. Now if there are two records identical and all shapes and form, then I just want to return one of them, doesn't matter which, since all fields are identical.

Thanks for response.

[weekend00]

that will be more simple:
SELECT uniSelectedContacts.addresses,uniSelectedContacts. cities,uniSelectedContacts.us_states_and_canada, max(uniSelectedContacts.zip_codes) as zip_codes,uniSelectedContacts.Th eName FROM uniSelectedContacts
group by
uniSelectedContacts.addresses,uniSelectedContacts. cities,uniSelectedContacts.us_states_and_canada,un iSelectedContacts.Th eName

[johnmerlino]

Thanks for response. I'm strangely getting duplicates where everything is same. I'm not sure if it's fault in query or I'm appending rows together later that happen to be duplicates.

[orange]

Thanks for response. I'm strangely getting duplicates where everything is same. I'm not sure if it's fault in query or I'm appending rows together later that happen to be duplicates.

Another way to approach this could be:

;Make another field in your table, call ZIP5 for example
;create an UPDATE query to fill this new field with
Left(yourZipField,5)
;Now ZIP5 will have a 5 digit zip
; use the ZIP5 field to resolve the duplicates
Just my 2 cents....