Compare 2 addresses

Topic

New Reply

Hi I need to do a match between table with street addresses and zip codes.
I can join the zipcodes easy enough but how can I compare the street names if the zip codes match.
I’m not concerned with the street number, just if the street name matches or is close.
I know streets like 1st street and first street wouldn’t be matched so there will be some manual work to be done after I get the results.

Thanks for any ideas,
Scott

Reply | Quote

Replies

Well, you have a challenge there. If you want to match strictly on the Street name, then I would do an expression using the InStr() function to locate the beginning of the Street name. You might also want to use the InStrRev() function to locate the end of the Street name, and get rid of things like ST or BLVD, etc. You should then be able to match those street names against a table of allowed Street Names, and then find those that don’t match. But any way you work it, unless your data is extremely clean and pretty small, you will have a sizeable manual effort on your hands. FYI, there are some commercial products that do address clean up against the USPS data, as well as some companies who provide a batch service to clean up a list of addresses.

Reply | Quote

Hi Scott, in my Dbs, I always clean addresses I enter. I use the table StreetCorrections to find the street abbreviation and replace it with the offered correct abbreviation. In your case, you could use that table to find the street part of you r addresses and remove them. You could also remove any numbers (Isnumeric() would be good for that). Then use Trim() to remove spaces. You should be able to do all that automatically with 1 – 3 queries. You will be left with the street name.
I have attached the table in StreetCorrections.xls. You should be able to import it to Access as a table.

Reply | Quote