CONTAINS using a keyword table

Question

I would like to create a query that populates a field in one table based on a keyword lookup (CONTAINS) referencing a keyword field from a different table. See sample tables below:

tbl_Parts
+----+------+-------------+------+
| ID | Comp |    Desc     | Type |
+----+------+-------------+------+
|  1 |  112 | Brg, Ball   |      |
|  2 |  245 | Bearing, X  |      |
|  3 |  364 | Mtg Ring, 1 |      |
|  4 |  445 | Pump, 2x3   |      |
+----+------+-------------+------+

tbl_Ref
+----+---------+-------+
| ID | Keyword | Type  |
+----+---------+-------+
|  1 | Bearing | O     |
|  2 | Ring    | S     |
|  3 | Pump    | P     |
|  4 | Disc    | O     |
+----+---------+-------+

Specifically, I would like to populate tbl_Parts.Type using tbl_Ref.Type WHERE tbl_Parts.Desc CONTAINS tbl_Ref.Keyword.

However, I have not been able to find any examples of the CONTAIN function that utilizes a field as reference. I would imagine the query would look similar to this:

SELECT *
FROM (
SELECT tbl_Parts.Comp, tbl_Parts.Desc, tbl_Ref.Type AS tbl_Parts.Type 
FROM tbl_Ref, tbl_Parts 
WHERE tbl_Parts.Desc CONTAINS tbl_Ref.Keyword
) AS x;

I know this is far from correct, but it's all I can conjure up at the moment.

There will also be instances where tbl_Parts.Desc contains multiple keywords. In this case, I would like to concatenate all type matches. However, this functionality is not priority at the moment.

It should be noted that I am okay with doing this in a lookup query within the Design of tbl_Parts, a standalone Query, or an Append Query.

Any advice or suggestions for this would be great, thanks in advance!

Answer 1

Since you ultimately intend to populate the tbl_parts.type field (presumably using an UPDATE query) with the value obtained from your SELECT query, you'll likely need to use a domain aggregate function such as DLookup to obtain the appropriate value, else MS Access will complain that the query will not be updateable.

To this end, I would suggest the following:

update tbl_parts p
set p.type = dlookup("type", "tbl_ref", "'" & p.desc & "' like '*' & keyword & '*'")

This will of course be scuppered if tbl_parts.desc contains quote symbols.

Example:

After running above SQL:

In the circumstance that the description matches multiple keywords and you need to concatenate multiple types into a comma-delimited string then I would suggest constructing a SELECT query with selection criteria based on the criteria demonstrated in the DLookup expression above, and then iterate over a recordset of the results using VBA.