how to find common street names

[Andrew Baillie]

I have a database of over 2000 addresses with the format ;
Address1 = 1 Smith St
Suburb = Collingwood
etc etc

What I want to do is group all the addresses with the same street name
together irrespective of the house number.

I don't have an individual list of street names so I cant use Like "*Smith*"

Any Suggestions

Tanks

 

[John Vinson]

I have a database of over 2000 addresses with the format ;
Address1 = 1 Smith St
Suburb = Collingwood
etc etc

What I want to do is group all the addresses with the same street name
together irrespective of the house number.

This can be difficult to do if you have anything other than
plain-vanilla addresses: for instance

2204 1/2 West Main St.
3018 W Main St.
2300 West Main
1210 Main St.

are all arguably "Main St" but it requires more intelligence than your
typical computer to deduce this fact!

Add to that that a friend of mine lives at

Bellacraine
Hosten Way

it can get very tricky figuring out WHAT the street is...

You can make a stab at it by considering a calculated field:

Street: Mid([Address], InStr([Address], " ") + 1)

This will have anomalies like "1/2 West Main St." and "W Main St." but
without either having a separate field for the street name, or doing a
LOT of sophisticated programming, it may be as well as you can expect.