Hi Beth, and welcome to the forum.
Do the customer names differ between the two reports simply because one report gives you, say, 11 characters of the name, and the other report gives you, say, 20 characters?
Or is there something else going on, like all of the Inc., Ltd., etc. get dropped? This would be pretty unusual, but stranger things seem to get programmed from time to time.
It's a common problem, and I'm afraid there's no magic bullet. Like you say, stemming the key field to the first word is often too crude a filter. But you can try a "fuzzy" approach - create a calculated field which stems to the first word, and one to the first two and so on, then create external lookups joining on these. Then filter the null values appropriately so that you get the most exact matches scoring highest - "ABC Company Inc of Michigan" might score 5, down to "ABC" matching scoring 1. You can always use the UPPER() and STRIP() functions to help get matches - you don't want Abc not to match A.B.C.
Watch out for running in to too many fields, exceeding the maximum data length, having too many external lookups in a project etc.
Thank you both for your help and advice -
Unfortunately, the names are indeed programmed differently in the 2 different reports. And with so many of them, I'm not sure a manual lookup table was going to help me much since the customer names could change month to month.
I may try the 'fuzzy' approach for a few of the known offenders and see how that goes.
Thank you both!
If you know you should be able to expect a match month my month you might consider first analysing just the two sets of names with a view to pre-preparing them for a later connection.
So, thinking 'aloud', to a match comparison for just the names (or names and anything else that might help!) between the two reports for a period. You might also 'normalise' the names, taking out spaces and punctuation for example, and check the results when the normalised fields are compared as well. One could do more perhaps but let's keep it simple for now.
If both checks are OK you would appear not to have a problem - though there might be exceptions to that but is there are then you have some real data matching issues anyway that would require much more rigorous attention!
If one matches but the other doesn't a small correction may be required. If a name has no matches it would need to be investigated. It is likely that relatively few unmatched records will appear if the data is at all reasonable.
From the resulting extractions you could build a simple look up file for use as an EXTERNAL lookup. Make this a notepad text file, or perhaps a simple Excel table and it would be easy to amend it as required - manually if necessary and therefore derive an accurate and usable cross reference table as a basis for future use. Presumably once created the lookup entry would have some consistency and use period by period so changes are likely to be low volume.
Just a few thoughts to mull over.
Just a thought, you mentioned that the names COULD change month to month. Does this mean many remain the same over time?
If that is the case I would make an outside table with a key field, and both variations of the customer names.
i.e. Customer Key 1, ABC Inc, ABC incorporated.
Then use this table every month and update as required. Then use a Monarch look up to see if you can find either spelling.
OR do the customers change so much that this would not be possible at all??