Need query for category keywords using tables

[msasteel]

Hello! I hope someone can help me!
I have a database with hundreds of records in a main table called tblPARTS which has three fields - Part Number, Part Description and Category.
I have another table, tblKEYWORDS, with two fields; a field called KEYWORDS which has words like "cable", "bracket", and "relay". The table has a second field called CATEGORY. The CATEGORY field has either the word ELECTRICAL or MECHANICAL associated with each of the KEYWORDS. So, for example, "bracket" would have "MECHANICAL" as its associated category. There are dozens of these entries in the KEYWORD table.

The problem: I need to auto fill the parts table (tblPARTS) with the correct category by looking at the DESCRIPTION field in the PARTS table which is made of several words, e.g. "STAINLESS STEEL BRACKET - 3" X 2" ". I need to find the word BRACKET and fill in the CATEGORY field in the parts table automatically via a query. There are several hundred parts in the main table.

I can do this easily using a standard update query, but it is limited to 10 IFF statements and there will be many more than this. Can I do this in VBA and if so, where do I start? I am an intermediate user of ACCESS, but I would appreciate as much detail as possible in any help one can give.

Thank you very much for the assistance!

Just wondering if anyone had any ideas here.... thanks.

 

[plog]

You wouldn't do this with a horribly nested Iif statement, you would simply need a InStr(http://www.techonthenet.com/access/functions/string/instr.php) call. If the keyword is contained in the description it returns a value greater than 0, if not it would return 0.

 

[msasteel]

The problem there is that I need to write the appropriate category (ELECTRICAL or MECHANICAL) into the field in the main parts table. If I use Instr then I have to translate the 1 or 0 so it can represent the correct category. Am I missing something here?

 

[jdraw]

I agree with plog, you wouldn't do this with a bunch of IIF statements. However, it would be helpful if you could describe the "big picture". What is the business in plain English?

What is the proposed use of the Category field? Can a part be in more than 1 category?
Did you consider some sort of hierarchy Something like major category, sub category, sub sub category?

Can you lead us through a scenario where you have some part with its description and you want to assign a category? What are the steps as you see them?

 

[plog]

Am I missing something here?

Yes and no. You're right, its not as simple as plugging InStr into a field and running a query. That InStr would go into a subquery which would help you identify which keywords go with each part.

Here's the part you are missing--a cartesian product query (http://en.wikipedia.org/wiki/Cartesian_product). You need a way to compare every product to every keyword, this is how you do it. Create a query based on tblPARTS and tblKEYWORDS.


SELECT [Part Description], [KEYWORD] FROM tblParts, tblKEYWORDS

Thats the query you need to add your InStr test to. It's going to match every part to every keyword and tell you which ones belong together. From there you would probably make a table off of it and then use that table to append data to tblParts.

My guess is that you are going to get multiple KEYWORDS for some parts and some parts without KEYWORDS. But that bridge will come after you set up your query.

 

[msasteel]

The overall picture: I am given a list of parts from Engineering with descriptions - hundreds for one project. I need to go through each part and categorize each part as MECHANICAL, ELECTRICAL, PNEUMATIC, PLUMBING, or DOCUMENT. This information is used for spare parts reports for our service technicians so Purchasing knows what to order and how many to support the field.
I submit a report to Purchasing with all the spare parts I deem required and I categorize them in the report as I noted above as part of the report. I don't enter the parts individually; I take the whole report as submitted to me to do this.
I am sure the solutions provided to me below will work, but to be honest, I will have to do some homework as I have never used this approach for anything. It is all new. I am more than willing to learn, but it might take a little while for me to try the proposals, so I beg your patience. I do appreciate the help. I submitted this same question on another known forum, but no one has even inquired. Lots of views, but no replies.

 

[msasteel]

Also, each part will only have on category. The descriptions are simple enough that I can determine the right category.

 

[msasteel]

The purpose of the category field is to track our costs for each of the categories.