normalizing addresses

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hello,

I need to normalize addresses in a database. The addresses are filled in one
column, but it should be divided into streetname, homenr, homesuffix.

E.G: teststreet 1a should be teststreet | 1 | a

If the address begins with 2nd teststreet 23a, it should be 2nd teststreet |
23 | a

I hope someone can help me?

Greetings,

J
 
Jason said:
Hello,

I need to normalize addresses in a database. The addresses are filled in one
column, but it should be divided into streetname, homenr, homesuffix.

E.G: teststreet 1a should be teststreet | 1 | a

If the address begins with 2nd teststreet 23a, it should be 2nd teststreet |
23 | a

I hope someone can help me?
Click to expand...

Translating addresses is not easy or straightforward. You will have to
do manual conversions whatever method you devise.

One approach could be:

* order by number of spaces (= number of words -1)
* visually inspect the structure per number of words; devise a
translator; mark exceptions (add a boolean field to the table for this)
 
Jason

Not sure I understand... Are you trying to parse a string that has more than
one "fact" in it? What "rule" would you use if you were going to explain it
to someone doing it manually? Can you use the string functions in Access
(see Left(), Mid(), Right() functions) in a query to "get" these pieces? If
so, you can add the new fields to your table and create an update query to
fill them.

Just in case, however, there's one/more of the addresses that don't exactly
follow the pattern, plan to have someone look over the results and "fix" any
mistakes!
 
Hi Jason,

This can be done - provided you can work out rules that specify how to
normalise each variation that may be encountered. Usually the best tool
for this sort of task is a regular expression engine; see e.g.
http://www.4guysfromrolla.com/webtech/090199-1.shtml for an
introduction.

The VBScript regular expression object described in that article can be
used in VBA as well as VBScript.
 
Back
Top