Answer

[Kevin]

For those of you interested, the problem was me trying to
warp SQL syntax. What was required was an additional join
on the lookup table so that the correpsonding three
records were available in the record in the result query.
The following works fine :

UPDATE Clarifications INNER JOIN (tblIssues INNER JOIN
tblClar_Status_to_Issue_Status ON
tblClar_Status_to_Issue_Status.Issue_Status =
tblIssues.Status) ON Clarifications.[Issue No] =
tblIssues.Issue_ID SET Clarifications.[CLAR STATUS] =
tblClar_Status_to_Issue_Status.Clarification_Status;