Update Field using Existing field Like another Existing Field in another Table......

[Nathan Lo]

I will try to explain this the best I can

I have a table that contains a Trouble Description field called TRBL_DESC , the Table we will call "IBC" in this field technicians have entered any combination of trouble descriptions, and from that I want to create categories based on keywords. I Created another Table with Keywords and Categories then populated that with the array of information I want to check against. Table name Cat_Table and lets call the Fields "Category" & "Keyword"

I thought that the end code to do what I want would be



Update IBC

SET Category = Cat_Table.Category
WHERE TRBL_DESC Like "%" +Cat_Table.Keyword + "%";

However, it appears that for some reason its not recognizing Cat.Table.Category and Cat.Table.Keyword as a table/field and I dont understand why, any help would be appreciated.
when I try to run this it asks me for the user entry as if instead of it being a table/field its an undefined variable.

[pbaldy]

The second table needs to be joined. I just did something similar, here's the SQL:

UPDATE CustomersForUpdate INNER JOIN Tbl_Customer ON (CustomersForUpdate.company_cd = Tbl_Customer.company_cd) AND (CustomersForUpdate.Cust_key = Tbl_Customer.Cust_key) SET Tbl_Customer.Email = [CustomersForUpdate].[Email];