Getting error 3073 when trying to run an update

[Wyoming]

Hello, I am just trying to run a (what I thought to be) quite simple update query, but when doing it I'm getting the error 3073.

The table to be updated is named LEAD_TEMP and has only 3 fields
ID_LEAD: Number, Primary Key
ID_ESTADO: Number
Descartado: Yes/No

The field to be updated is [ID_ESTADO]

The new value is taken from the query EstadosHistoricos_AUX3. This query outputs 2 fields: ID_LEAD, ESTADO_FECHA
This query looks like this:

Code: Select all

SELECT EstadosHistoricos_AUX2.ID_LEAD, IIf([ID_ESTADO] Is Null,1,[ID_ESTADO]) AS ESTADO_FECHA
FROM EstadosHistoricos_AUX2 LEFT JOIN ACCIONES ON EstadosHistoricos_AUX2.MáxDeID_HCO_LEAD = ACCIONES.ID_HCO_LEAD;

So I want to update the ID_ESTADO field with the value of ESTADO_FECHA. The update query looks like this:

Code: Select all

UPDATE LEAD_TEMP INNER JOIN EstadosHistoricos_AUX3 ON LEAD_TEMP.ID_LEAD = EstadosHistoricos_AUX3.ID_LEAD SET LEAD_TEMP.ID_ESTADO = [ESTADO_FECHA];

But when I run this query I get the error 3073. Any ideas to sort this out?

thanks!

[HansV]

Error 3073 is "Operation must use an updateable query."
The field name EstadosHistoricos_AUX2.MáxDeID makes me suspect that EstadosHistoricos_AUX2 is a Totals query. Any query based directly or indirectly on a Totals query is not updateable and hence cannot be used in an update query.

A workaround is to change EstadosHistoricos_AUX3 to a Make-Table query that creates a temporary table, and to use the name of the temporary table in the final query instead of EstadosHistoricos_AUX3.

[Wyoming]

Ok, I was trying to avoid the creation of temp tables but it seems it is the only alternative.
Thanks a lot for your advice.