sql - Access operation must use an updateable query -

sql - Access operation must use an updateable query -

i have been racking brain trying work day, , it's there dreaded "operation must utilize updateable query". after multiple changes , rewrites have ended following:

update users u inner bring together (select up.[agent id] agentid, up.[last name] lastname, up.[first name] firstname, up.[team id] teamid, up.[security profile id] secprofid, e.emailid, up.[username] [username], iif(up.status = 'active', 1,0) [status], l.locationid, nz(dd.dateid, 1) hiredate, iif(up.[rehire status] = 'eligible', 1, 0) rehireeligible, (select d.dateid datecodes d d.datevalue = format(now(), "short date")) modifieddate ((userspaste left bring together datecodes dd on up.[hire date] = dd.datevalue) inner bring together emailaddresses e on up.[email address] = e.emailaddress) inner bring together (select l.locationid, t.timezone, l.country, l.state, l.city locations l inner bring together timezones t on l.timezoneid = t.timezoneid) ll on up.[time zone] = ll.timezone , up.country = ll.country , up.state = ll.state , up.city = ll.city) uu on u.agentid = uu.agentid set u.lastname = uu.lastname, u.firstname = uu.firstname, u.teamid = uu.teamid, u.secprofid = uu.secprofid, u.emailid = uu.emailid, u.[username] = uu.[username], u.[status] = uu.[status], u.locationid = uu.locationid, u.hiredate = uu.hiredate, u.rehireeligible = uu.rehireeligible, u.modifieddate = uu.modifieddate

now, inner select query forming uu outputs how users table set up. in mind, , right me if i'm wrong, should work same if joining straight table.

do need create actual table, , insert records formed uu table, update based off new table? if have to, will, prefer not that. know of other way or need work?

in case, yes, think you'll need convert subquery make-table query

select ... uu ...

and have update query as

update users u inner bring together uu on ...

in simple cases "updateable query" issue can avoided using domain aggregate function(s). illustration next query refuses run ("operation must utilize updateable query.")

update donors inner bring together ( select donorid, sum(amount) sumofamount donations grouping donorid ) totals on donors.id = totals.donorid set donors.totalamount = totals.sumofamount

but next query works

update donors set donors.totalamount = dsum("amount", "donations", "donorid=" & id)

however, case sufficiently complex using temporary table best approach.

sql sql-update ms-access-2013