match data of one field with part of data of another field.

[Guest]

Hello,

I need to match the data of one field of a table with part of a field of
another table. This data can be in any part of the field.

 

[Allen Browne]

This is not a relational design, and will not work efficiently.

A basic rule of normalized data design is to make each field atomic. This
means each field contains only one thing, and so you will not be matching
part of one field against another.

If you want to do it anyway, try a query like this:
SELECT tClient.Surname
FROM tClient INNER JOIN tClient_1
ON "*" & tClient.Surname & "*" Like "*" & tClient_1.Surname & "*";

The example pulls up surnames that overlap between 2 tables.