Query must use updatable query

[flaviooooo]

Hi,

I am having trouble using an updatequery between a table and a query:

UPDATE MATERIAAL_DOORGANG INNER JOIN Q_OPM_PER_MAXMATDOORGANG ON 
(MATERIAAL_DOORGANG.DOORGANG = Q_OPM_PER_MAXMATDOORGANG.DOORGANG) 
AND (MATERIAAL_DOORGANG.MATERIAAL = Q_OPM_PER_MAXMATDOORGANG.[MATERIAL NUMBER]) 
SET MATERIAAL_DOORGANG.OPMERKING_ID = [Q_OPM_PER_MAXMATDOORGANG]![OpmerkingID]

when I run this I get the message that I need to use an updatable query.
I know I can workaround this by using a temp-table instead of the query, but frankly I am tired of doing this workaround. Is there really no other way??

Thanks in advance

Kind regards,
Fabian

 

[rac2]

UPDATE MATERIAAL_DOORGANG
INNER JOIN Q_OPM_PER_MAXMATDOORGANG ON
(MATERIAAL_DOORGANG.DOORGANG = Q_OPM_PER_MAXMATDOORGANG.DOORGANG)

AND (MATERIAAL_DOORGANG.MATERIAAL = Q_OPM_PER_MAXMATDOORGANG.[MATERIAL NUMBER])

SET MATERIAAL_DOORGANG.OPMERKING_ID = [Q_OPM_PER_MAXMATDOORGANG]![OpmerkingID]

Is this a one-time-only process? Or is it a step that is part of a process of adding rows to the tables? It looks like the tables would be linked by OpmerkingID if that were filled in MATERIALL_DOORGANG. And you are trying to fillin the value based on matching DOORGANG and MATERIAAL values.

If it is a process, can you change the process so that the row is first inserted in one table, the OpmerkingID or the OPMERKING_ID, whichever is generated by the insert, is then inserted in the other table. Thus eliminating the need to update MATERIAAL_DOORGANG.

Possibly the query is not updatable because the join on DOORGANG and MATERIAAL yields multiple matching rows, in other words the combined values are not unique, hence there are multiple rows with multiple values of [Q_OPM_PER_MAXMATDOORGANG]![OpmerkingID]. Just a thought.

 

[flaviooooo]

I'll clarify the need of the query:

THE QUERY Q_OPM_PER_MAXMATDOORGANG checks in an Order-table, and gives me per Materiaal-Doorgang the latest OPMERKING_ID.
Since several orders may have the same Materiaal-Doorgang, but with another OPMERKING_ID, the query is designed to find and show only the last one.

Afterwards, the Code in the eaxmple fills in this OPMERKING_ID into the table MATERIAAL_DOORGANG, based on the Keyfields MATERIAAL and DOORGANG.

I hope this makes sense?

Kind regards,
Fabian

 

[cheerio]

UPDATE TABLEA JOIN TABLEB
ON ...
SET

Access gets confused as to which table you are trying to update

UPDATE TABLEA
SET FIELD1 = ....
FROM TABLEA JOIN TABLEB ON ...
WHERE ...

should work as the target table is clear.

TABLEA gets two mentions - once as a target and once as a source.

 

[rac2]

Is MATERIAAL_DOORGANG.OPMERKING_ID null in the one row that you wish to update?
If so, and if Access supports correlated subqueries

UPDATE MATERIAAL_DOORGANG
  SET MATERIAAL_DOORGANG.OPMERKING_ID = (
          SELECT OpmerkingID
          FROM Q_OPM_PER_MAXMATDOORGANG
          WHERE 
             (MATERIAAL_DOORGANG.DOORGANG = 
              Q_OPM_PER_MAXMATDOORGANG.DOORGANG)
            AND 
             (MATERIAAL_DOORGANG.MATERIAAL = 
              Q_OPM_PER_MAXMATDOORGANG.[MATERIAL NUMBER])
         )
WHERE MATERIAAL_DOORGANG.OPMERKING_ID IS NULL

Also you might get some further ideas from the Access Help, ask the question "When can I update data from a query".