Joining 2 Tables on Fields that are only partially similar

[fm8443]

I have a PRODUCTS Table (1000 rows) containing a field called NAME.
I also have an ATTRIBUTES Table (20,000 rows) with useful attribute info (in 3 fields e.g. F1, F2, F3) that I want in my PRODUCTS table layout (for export to Excel).

I can't see an easy way to join the 2 tables.

ATTRIBUTES::MOD_NAME is never exactly the same as PRODUCTS::NAME.
ATTRIBUTES::MOD_NAME is a string that is sometimes partially contained in the string that is PRODUCTS::NAME.  This will be true zero, one or a few times.

ATTRIBUTES::MOD_NAME is contained in one or more of the PRODUCTS::NAME -- that's the "join" I want to do (based on a calculation/function e.g. StrMatch from https://www.briandunning.com/cf/257 or similar.) 

How do I do this "join"?  Or is there a better way to do what I am looking for? Thanks.

Edited September 26, 2016 by fm8443

[comment]

41 minutes ago, fm8443 said:

I can't see an easy way to join the 2 tables.

There isn't.

If your data is static, I suggest you clean it up. For each attribute, find the product (or can it be products?) that contains its name and store its ID in a match field in the Attribute table. Instead of a find, you could also use the ExecuteSQL() function with the LIKE operator. But you cannot use a calculation field, because it would have to be unstored, and therefore unusable as a matchfield.

 

Edited September 26, 2016 by comment

[fm8443]

The same ATTRIBUTES::F1 etc. may actually match a few of the PRODUCTS::NAME rows.

(I may be able to add a field to PRODUCTS that calculates a string that can actually be matched to the ATTRIBUTES table).

Edited September 26, 2016 by fm8443

[bruceR]

"for export to Excel...."

Why would you want to do that?