I often get tripped up doing an update of one table from fields in other table in access sql. i'm not sure if its because its different in other sql's, but in access it is required to do an inner join to the table which is being updated within the foreign update query.
Here is the general syntax in ms access sql to do one of these babies:
UPDATE [toTable] INNER JOIN [fromTable] ON [toTable].[primaryKey] = [fromTable].[foreignKey] SET [toTable].[toFieldUpdate]] = [fromTable].[fromFieldUpdate];