Access 97 lookup/update query question

  • Thread starter Thread starter Larry Wade
  • Start date Start date
L

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?
 
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
 
Back
Top