Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Atlanta, Georgia, USA
    Thanked 0 Times in 0 Posts
    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,

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts
    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.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Patterson Lakes, Melbourne, Victoria, Australia
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts