MS Access SQL: Replacing a field with a string + another row value -

i trying update table in ms access database contains movie information

      [table:movies]     moviename         crewid      crewmember     big lebowski        1       joel coen     big lebowski        2       ethel coen     big lebowski        3       carter burwell     big lebowski        4       roger deakins     matrix              1       andy wachowski     matrix              2       lana wachowski     matrix              3       don davis     matrix              4       bill pope  

crewid 1 director , 2 co/assistant director, , on.

what trying replace co-director name in 'crewmember' column "assistant of director name", below

      [table:movies]      moviename         crewid      crewmember     big lebowski        1       joel coen     big lebowski        2       assistant of joel coen     big lebowski        3       carter burwell     big lebowski        4       roger deakins     matrix              1       andy wachowski     matrix              2       assistant of andy wachowski     matrix              3       don davis     matrix              4       bill pope  

i using following query giving syntax error (missing operator).

      update t1     set t1.crewmember = 'assistant of '+ t2.crewmember     movies t1, movies t2     t1.moviename = t2.moviename     , t1.crewid = 2     , t2.crewid = 1;  

please me query

try :

update movies t1, movies t2 set t1.crewmember =  'assistant of ' + t2.crewmember t1.moviename=t2.moviename , t1.crewid=2 , t2.crewid=1