Access 97 lookup/update query question

[Larry Wade]

Access97 question: Person Table has a field CompanyID which is indexed and
Memo field which is Text.. Company Table has field CompanyID which is
indexed and CompanyName which is indexed.

Is there a way with an Update Query to take the Person Table Memo Field
(which contains a company name), look it up in the Company Table (Company
Name), retrieve the CompanyID from the Company Table and put that CompanyID
in the Person Table?

 

[John Spencer (MVP)]

This SELECT might give you what you want. It can't be written in the grid since
it is not an equals join. No guarantee that these will work since I cannot test them.

SELECT C.CompanyID, P.*
FROM Company as C INNER JOIN Person as P
ON P.MEMOField LIKE "*" & C.CompanyName & "*"

An UPDATE would look something like:

UPDATE Company as C INNER JOIN Person as P
ON P.MEMOField LIKE "*" & C.CompanyName & "*"
SET P.CompanyID = C.CompanyID
WHERE P.CompanyID is Null <-- Optional line if you want to only update blanks