UPDATE Person 
SET TeamLeaderId = 
 (SELECT LeaderPersonID 
  FROM DBRES_Person_TeamLeader_IDList 
  WHERE Person.PersonID = DBRES_Person_TeamLeader_IDList.PersonID
 );

SELECT [TBL_EC Specialists].[Last Name], [TBL_EC Specialists].[First Name], 
  [TBL_EC Specialists].[Team Leader], P1.PersonID, P2.PersonID AS LeaderPersonID
FROM ([TBL_EC Specialists] 
INNER JOIN Person AS P1 ON 
  ([TBL_EC Specialists].[First Name] = P1.FirstName) 
   AND ([TBL_EC Specialists].[Last Name] = P1.LastName)) 
INNER JOIN Person AS P2 ON 
  ([TBL_EC Specialists].[Team Leader]) = (P2.LastName & ', ' & P2.FirstName)
WHERE ((([TBL_EC Specialists].[Team Leader]) Is Not Null));

UPDATE Person 
SET TeamLeaderId = LeaderPersonID 
  FROM DBRES_Person_TeamLeader_IDList 
  INNER JOIN Person
  ON Person.PersonID = DBRES_Person_TeamLeader_IDList.PersonID
;

UPDATE ([TBL_EC Specialists] 
INNER JOIN Person AS P1 
  ON ([TBL_EC Specialists].[Last Name] = P1.LastName) 
  AND ([TBL_EC Specialists].[First Name] = P1.FirstName)) 
INNER JOIN Person AS P2 
  ON ([TBL_EC Specialists].[Team Leader]) = (P2.LastName & ', ' & P2.FirstName) 
SET P1.TeamLeaderID = P2.PersonID;

quote:

---

Originally posted by AjarnMark

And for the record, y'all will notice that two of the things I'm getting rid of in this restructuring project are the TBL_ prefixes and the spaces in table and field names.

---