Addresses

T

Tristan

I need to create an application to make addresses standard. Every department
uses their own and when it is time for me to do the analysis is hard to
compare, remove duplicates, etc because all the addresses are written
differently. For Example:
(1) 102 MT Gleason Av (2) 102 Mount Gleason Av (3) 102 MT Gleason Ave (4)
Mount Gleason Ave. How can I compare all of them and make sure only one is
kept in my database. It is the same address but written in different formats
by various departments.
 
J

Jerry Whittle

Are they all linked to a common field like EmployeeID? If so write a query
that sorts the records by EmployeeID then the Address field. You could even
do a Totals query which groups by EmployeeID and does the Max or Last of the
Address field.
 
J

John W. Vinson

I need to create an application to make addresses standard. Every department
uses their own and when it is time for me to do the analysis is hard to
compare, remove duplicates, etc because all the addresses are written
differently. For Example:
(1) 102 MT Gleason Av (2) 102 Mount Gleason Av (3) 102 MT Gleason Ave (4)
Mount Gleason Ave. How can I compare all of them and make sure only one is
kept in my database. It is the same address but written in different formats
by various departments.
Click to expand...

You'll really need to use the USB interface - Using Someone's Brain - to clean
up this mess. Where I'd start is by creating some new fields: AddressNo (e.g.
102), Direction, Streetname, Suffix (e.g. Ave). You should be able to get a
list of all the streets in your area - try the city assessor's office, for
example.

You can then use a Form with combo boxes for direction (allow for NULL of
course), streetname, and suffix to enforce consistancy. You can use a
combination of Update queries such as

UPDATE yourtable
SET Address = Address & "e"
WHERE Address LIKE "* AV"

to clean up the most frequent offenders, with a manual process of scrolling
through the addresses on a Form and selecting the correct names from the
combos.

John W. Vinson [MVP]
 
Top