Eliminate Multiple Data - not simple DIstinct way!!

[r4echer87]

Hi Everyone,

i have a ms access database consisting of a lot of adresses.

Due to the fact, that some people are typing one and the same adress in a different way, i created a key (StoppID) consisting of the Postalcode + first4 characters from the company name + first 5 characters of the street. The main reason why i do that is because i want to import there adresses into MapPoint to locate the adresses. But i don't want him to locate one and the same adress for multiple times. So i want an Query to eliminate multiple adresses.

The Query should combine every distinct StoppID with one random existence of postalcode, street, city and country.Because of the fact that SToppID may occur multiple times, and also with different way of typing the street or/and city or/and company. i want access to choose one opprtunity!



Is that possible to do? If yes how?

Best Regards,
Alex

[orange]

There have been whole careers associated with "cleansing" name and address files. This is not a trivial subject, and once you think you have it figured out, you encounter names like Del'Oro.
Anyway, your major issue, in my view, is the part where

people are typing one and the same adress in a different way

which is a data entry procedure.

Why not try to change the ability to do it differently, or parse it to make it the same or at least similar? How did you choose 4 and 5 characters -- maybe 6 and 7 will give better performance...

There are many commercial tools and various Postal Authorities offer services -- so bottom line there is no simple solution to this.

If you are intent on doing as good a job as practical, then break it into steps and reduce duplication and variation at each step.