By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Keyword Lookup - Concatenate All Matches - Update Table

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

Expand|Select|Wrap|Line Numbers
  1. Data - Inventory
  2. +----+------+-------------+------+
  3. | ID | Comp |    Desc     | Type |
  4. +----+------+-------------+------+
  5. |  1 |  112 | Brg, Ball   |      |
  6. |  2 |  245 | Bearing, X2 |      |
  7. |  3 |  364 | Mtg Ring, 1 |      |
  8. |  4 |  445 | PumpBearing |      |
  9. +----+------+-------------+------+
Expand|Select|Wrap|Line Numbers
  1. Component Library
  2. +----+---------+-------+
  3. | ID | Keyword | Type  |
  4. +----+---------+-------+
  5. |  1 | Bearing | O     |
  6. |  2 | Ring    | S     |
  7. |  3 | Pump    | P     |
  8. |  4 | Disc    | O     |
  9. +----+---------+-------+
Expand|Select|Wrap|Line Numbers
  1. Data - Inventory  (DESIRED)
  2. +----+------+-------------+------+
  3. | ID | Comp |    Desc     | Type |
  4. +----+------+-------------+------+
  5. |  1 |  112 | Brng, Ball  |  O*  |
  6. |  2 |  245 | Bearing, X2 |  O   |
  7. |  3 |  364 | Mtg Ring, 1 |  S   |
  8. |  4 |  445 | PumpBearing | P, O |
  9. +----+------+-------------+------+
* Fuzzy match is not necessary but would be great to implement in the future.

Specifically, I would like to populate [Data - Inventory].Type using [Component Library].Type where [Data - Inventory].Description contains a [Component Library].Keyword

I have used the query below to generate a full list of all matches found, along with keyword and its associated type.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Data - Inventory].Component, 
  2. [Data - Inventory].Description, 
  3. [Component Library].Keyword AS [Key], 
  4. [Component Library].Type AS PartType
  5. FROM [Component Library] 
  6. LEFT JOIN [Data - Inventory] 
  7. ON [Data - Inventory].Description LIKE "*" &  [Component Library].Keyword & "*"
  8. ORDER BY [Data - Inventory].Component;
I have then attempted to concatenate all matches (comma delimited), grouped by Component, using a Concat module found in previous threads (see query below). The concatenation works when referencing a table identical to the previous query results, but only returns one match when using a query or subquery in the FROM clause.

Expand|Select|Wrap|Line Numbers
  1. SELECT Component, 
  2. Max(Concat(Nz(Component), Nz(PartType))) AS Types
  3. FROM [All Matches]
  4. GROUP BY Component;
The end goal is to update [Data - Inventory].Type with this data, which may or may not be possible along this route.
Any advice or suggestions for this would be great, thanks in advance!
Jan 4 '19 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.