I have two sets of company names (IBM and IBM, Inc.) which I need to match. One set is in an MSAccess table entitled 'WIP' with a field name 'MASTER_COMPANY_NAME' and the second set is in an MSAccess table entitled 'CORP' with a field name 'COMPANY'.
I am looking for a reasonably simple way to match them -- perhaps scoring each match.
Try doing an internet search for the rules behind something called "Soundex". Somewhere you will find the rules for matching names (or any alpha fields) by "sounds like" rather than actual text. You can then program the rules in vba and get a fairly good match. I believe that SQL Server has a built in function to do this, but not Access.
For what it's worth, The Access Cookbook, 2nd Edition from O'Reilly comes with an example query that performs Soundex comparisons if you can spare the cash and want to save yourself some time, it may help.
It's also an awesome book to have around. Good luck.
I think it may be the best way to take care of your situation unless there are any more specific differences between the two columns. How many records are you talking about? Is this a one time conversion, or are you looking to join on these columns on a regular basis?