DUPLICATE ADDRESS PROBLEM

[osimini1]

Please can somebody help or direct me on how to solve this problem with duplicate address A suitation where a provider_ID have two addresses in the same address, one with suite number and other without suite number. I want to issolate or remove the duplicate address without suite number. I have tried using SQL scripts but unable. Attached below is the sample data. I would appreciate any help that I can get.



ID FirstName LastName Address City Zip
AB74960 PETER AMES 1818 N ORANGE GROVE AVE STE 5000 POMONA 91767
AB74960 PETER AMES 1818 N ORANGE GROVE AVE POMONA 91767
AB74960 PETER AMES 12360 PACIFIC AVE LONG BEACH 90806
CK55566 KATHY TOBINS 2777 PACIFIC AVE STE E LONG BEACH 90806
CK55566 KATHY TOBINS 17660 LAKEWOOD BLVD BELLFLOWER 90706
CK55566 KATHY TOBINS 2360 PACIFIC AVE STE 200 LONG BEACH 90806
CK55566 KATHY TOBINS 2777 PACIFIC AVE LONG BEACH 90806
GKL81919 KATE PAST 1000 W WHITTIER BLVD MONTEBELLO 90640
GKL81919 KATE PAST 3030 TYLER AVE STE 12 EL MONTE 91731
GKL81919 KATE PAST 1000 W WHITTIER BLVD STE 34 MONTEBELLO 90640
GKL81919 KATE PAST 3030 TYLER AVE EL MONTE 91731

 

[SkipVought]

I posted an answer in your duplicae post in forum68.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

How are ya osimini1 . . .

There's no way a [blue]PrimaryKey[/blue], which I believe to be [blue]ID[/blue] can have two different addresses!. If you can ... then you have a [blue]table normilization problem[/blue].

Please post some assemblence of your table structure!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

Based on your data, one approach might be to simply assume the one with the longer Address is the correct one.

One thing that makes this more problematic is that you don't appear to have any unique ID in this table. I'm assuming this is an "Addresses" table and not a "Customers" table (although if it is First Name and Last Name properly belong in "Customers" and not "Addresses"). Sometimes it may seem that in a child table, having a primary key is not necessary. However, it is for just such an occasion as this that it is always useful to have a primary key - as an easy way to identify any particular record.