Only Match the first 10 characters...

[mjbryant]

I have two files with company names. The company names in each file have been entered over the past year by numerous staff employees. Sometimes, the names entered are not consistent. For example, one person might type "Calhoun Apts" in one file and then one person might type "Calhoun Apartments" in the other file. Therefore, they are not a one to one match.

I want to only match the first 7 or so characters between each file. How do I do this?

 

[Corticus]

You'll need your two files together in one, firstly.

Then, make a query off each table, and add a field like:
Left7: left([myField],7)
to each query.

Then, make a third query, and join the two queries on this new field.

That will show the records in which the first seven letter in both files match.. now what you choose to do once you know this, you'll have to tell us.

 

[mjbryant]

Thank you so much! I'm determining how to approach this now that I have the data. I will definitely let you know if I come up with something effective.

 

[mjbryant]

Here's what I did in the access database to the file I created with the expression to extract only the first 7 characters:

1) I copied and pasted the file - structure only
2) I made the original column of names a primary key
3) I appended the original file into the structure only from #1 (this was to ensure there were no duplicate names)
4) I did find duplicates query on the column that i extracted the first 7 letters out of the name

this way, i have isolated those names that share the first 7 characters - now it's just a manual look-through to find like names. i'm still looking for a better process...