ms access - SQL Updating from an inner join

Question

I'm using microsoft Access.

If this query:

(SELECT FERMENT.FermentId
FROM FERMENT
INNER JOIN [BELGIUM BEER]
ON
FERMENT.FermentName = [BELGIUM BEER].FermentId ORDER BY [BELGIUM BEER].BeerId) a

returns FermentId, how do I update a different table with that column?

Example:

UPDATE EXAMPLETABLE
SET EXAMPLETABLE.FermentId = a.FermentId
FROM a
(SELECT FERMENT.FermentId
FROM FERMENT
INNER JOIN [BELGIUM BEER]
ON
FERMENT.FermentName = [BELGIUM BEER].FermentId ORDER BY [BELGIUM BEER].BeerId) a

Answer 1

Not sure what the relation of EXAMPLETABLE with your data is, but in general.

In Access the SET part is after the join, also skip the select part en the order by. Should be something like this

UPDATE FERMENT
INNER JOIN ([BELGIUM BEER]  ON FERMENT.FermentName = [BELGIUM BEER].FermentId) 
SET EXAMPLETABLE.FermentColumn = a.FermentColumn

If it doent work try building the join in the query builder