Selecting records based on partial match

[Jamie Nicholson]

I am attempting to select the appropriate records for our
City from a very large database from the Ohio BMV (over
380,000 records). The field I am attempting to match is
a street address. I've been able to extract the street
name into a single colum, but the data has been entered
over the years in multiple forms (such as First Ave or
1st Ave). I've created a table that I would like to
cross reference that includes the critical portions of
each of our City's street names. However I am not sure
how to make it work. I have tried in the query itself,
but there are too many streets to make that work. Any
thoughts or suggestions?

Thanks,

 

[Gary Walter]

I am attempting to select the appropriate records for our
City from a very large database from the Ohio BMV (over
380,000 records). The field I am attempting to match is
a street address. I've been able to extract the street
name into a single colum, but the data has been entered
over the years in multiple forms (such as First Ave or
1st Ave). I've created a table that I would like to
cross reference that includes the critical portions of
each of our City's street names. However I am not sure
how to make it work. I have tried in the query itself,
but there are too many streets to make that work. Any
thoughts or suggestions?

Hi Jamie,

I might just add a field to the table (say "StreetName")
if you can (or maybe you already have).

Extract the street name into this field from the address field.

Then, maybe, set up your cross reference table like

IfIs WantToBe
First Ave 1st Ave
1st Avenue 1st Ave
First Avenue 1st Ave

Then run an update query joining your
original table to the cross reference table
on originaltable.StreetName = crossref.IfIs
setting originaltable.StreetName to crossref.WantToBe.

UPDATE originaltable
INNER JOIN crossref
ON originaltable.StreetName = crossref.IfIs
SET originaltable.StreetName = crossref.WantToBe;

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter