Almost the same field

[derrickchanson]

Here is my problem, I'm guessing there is not a way to solve it. I have two tables from two different sources. Neither table has a primary key or like field that I can use to create a relationship. I want to use address to identify names that appear on both lists, but there are multiple ways to write the same address, so as you can see I have a problem.

Is there some expression I can use in a query to identify people found in both tables based on "address" field? I'm wondering if there is a way to create a relationship between two records if the first 20 characters of a field are the same.

If this can't be done, I'll need to do a "find/replace" on every possible way to abbreviate street, avenue, etc...which will be very time consuming considering each table has aroun 500K records.

Thanks for the help,
Derrick

 

[JeremyNYC]

Derrick,

Consider adding a field to your tables and updating them with the first 20 characters of that field. Then you can link on that.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.