Update from another table

[NT100]

I want to update "Fellow" field in tblTAvail_FPA from the records in qry_Fellow_unique

I built a query update (qryU_fellow) in ACCESS 2016. That's

UPDATE tblTAvail_FPA SET (FPA.Fellow = -1) FROM tblTAvail_FPA, qry_Fellow_unique
INNER JOIN tblTAvail_FPA, qry_Fellow_unique ON tblTAvail_FPA.TRef = qry_Fellow_unique.TRef

qry_Fellow_unique is shown below
"SELECT DISTINCT qry_Fellow.TRef
FROM qry_Fellow
WHERE fellow = -1"

e.g. tblTAvail_FPA has TRef(=1,2,3,4,5,6,7,8,9,10), qry_Fellow_Unique has TRef(=2,5,8,9)



I wonder that there's is a syntax error with query "qryU_fellow".

What would you suggest to build the query to avoid the syntax error

Thank you in advance.

 

[isladogs]

Your update query is incorrect. Try

UPDATE tblTAvail_FPA INNER JOIN qry_Fellow_unique ON tblTAvail_FPA.TRef = qry_Fellow_unique.TRef SET tblTAvail_FPA.Fellow = -1

 

[June7]

I know can't use GROUP BY query in an UPDATE action and I seem to remember same for DISTINCT. Should get "Must use an updatable query" error. DISTINCT and GROUP BY queries are not updatable. I know, doesn't seem to make sense but that's the way it is.

 

[isladogs]

Well spotted June ....but even if DISTINCT is removed the original syntax is still incorrect

Allen Browne has a list of reasons why queries are not updateable here: http://www.allenbrowne.com/ser-61.html

 

[NT100]

Your update query is incorrect. Try

UPDATE tblTAvail_FPA INNER JOIN qry_Fellow_unique ON tblTAvail_FPA.TRef = qry_Fellow_unique.TRef SET tblTAvail_FPA.Fellow = -1

How come the query pops the following error.
Operation must use an updateable query

 

[isladogs]

Read posts 3 & 4

 

[June7]

Saving calculated data, especially aggregate data, is usually unnecessary and potentially dangerous. Calcs can become 'out of sync' with raw data. Calculate when needed and use the query for reporting results, do not save to table.