Use of the LookUp Command - Please Assist

[Tamrak]

Good afternoon,

I have two tables, unrelated.

Table A has the following fields:

* Explanation (Text)
* New Value (Text)

Table B, reference table, has the following fields:

* Descriptions (Text ) – Primary Key – no duplicate
* Value (Text)


What I would like to do, with the update query:

* If the substring words in Table A have been found “matching” the description in Table B, dump the Table B.Value into the field TableA.New Value. (Substring or word can be located anywhere in that Explanation field.)


For example:

Table A Explanation New Value

" Tomorrow we needs to find the Volvo to replace the defective vehicle", (blank in the New Value field)


Table B Descriptions Value

Volvo A123
Volkswagen A125

When running this update query, the value of “A123” will be dumped into the New Value field in Table A.

I think the lookup command might work. I am uncertain how to set the relationship, since these two tables are not related.

I do appreciate your assistant regarding this lookup command. Thanks.

 

[PHV]

UPDATE [Table A] INNER JOIN [Table B] ON [Table A].Explanation Like '*' & [Table B].Descriptions & '*'
SET [Table A].[New Value] = [Table B].Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tamrak]

PHV

Thank you for your reply. I do not have the access to my work computer right now. Just want to ask you whether this query will work only for one record, or it will perform the update, record by record, until the end of the last record.

I have approximately 500 records in Table A, that will need to update the record.

I will test your query when the computer system is up. Please let me know. Thanks.

 

[dhookom]

PH,
Do you think the query is going to be updateable? Usually the join must contain a primary key on one side. I was thinking the use of DLookup() might be necessary to get the value from Table B.

Duane
Hook'D on Access
MS Access MVP