Matching across two tables

[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

[HiTechCoach]

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