-
Matching across two tables
Hi
Have an interesting problem.
A client N/A table is running under Access and the requirement now is to integrate an existing Excel system. The challenge is that the Excel Customer Names are not always entered in the same format as the Access table. (There is no relationship based on Client ID)
Many are simple omissions of words e.g. - Instead of ABC Co Ltd in the Access table it is ABC in the Excel worksheet. The net result is that many Excel Clients are rejected.
Is there any way that one could search omitting some info such as "Co Ltd" so that only the main part of the client names is compared? Then all I need to do is append the unmatched Excel clients.
Regards
Tony Randell
-
Originally Posted by trandel
Hi
Have an interesting problem.
A client N/A table is running under Access and the requirement now is to integrate an existing Excel system. The challenge is that the Excel Customer Names are not always entered in the same format as the Access table. (There is no relationship based on Client ID)
Many are simple omissions of words e.g. - Instead of ABC Co Ltd in the Access table it is ABC in the Excel worksheet. The net result is that many Excel Clients are rejected.
Is there any way that one could search omitting some info such as "Co Ltd" so that only the main part of the client names is compared? Then all I need to do is append the unmatched Excel clients.
Regards
Tony Randell
You could try using this criteria in a query:
[Access Name] Like "*" & [Excel name] & "*"
You might also look into using Fuzzy logic. See: SoundEx
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|