Operation must use an updateable query

[NSAMSA]

Hi all:

Hopefully this is a quick fix. I'm trying to create an Update table for an Audit database and I am receiving the error in my title. I'm not certain what makes this different than other update queries I have made. As an example,

the following update query worked:

UPDATE tblTarget LEFT JOIN qryHeldTags ON tblTarget.TAG_ID = qryHeldTags.TAG_ID SET tblTarget.Notes = "Delete"
WHERE (((qryHeldTags.TAG_ID) Is Null));

qryHeldTags was simply a query where I took an ODBC connected table with TAG_ID as a unique factor, and I made the Status criteria as "RESELECT PEND" The purpose of this query is to make a factor for my delete query to find and remove records that no longer exist.

The following query gives me the error:

UPDATE tblAudit LEFT JOIN qryAuditEx ON tblAudit.CombField = qryAuditEx.CombField SET tblAudit.Archived = "Yes"
WHERE (((qryAuditEx.CombField) Is Null));

qryAuditEx is a union query from a linked table from another Access Db where I include CombField, a calculated field that combines three fields, ([Course] & "/" & [Department] & "/" & [ID]). The same calculated field exists in the tblAudit to find records where those three fields coordinate. The qryAuditEx gives me my desired records. However, when I try to run the report, I am told that it is not updateable.

Both these update queries seem to go by the same structure, so why is one incapable of updating?

Thank you for any help!

 

[Ranman256]

Some qrys are not updatable, like
You can't update from a sum qry.
Nor can you delete from a joined qry.

You CAN write the sums to a table then update from that.

 

[NSAMSA]

So, I'm going to have to make an append query to store the data I want to delete into a separate table and use that table as the basis for my update criteria from which to delete archived data? It's a long way around, but I agree in that it's the simplest solution and seemingly the only one.

 

[jdraw]

Allen Browne offers some info on read only queries here.

 

[Galaxiom]

The following query gives me the error:

UPDATE tblAudit 
 LEFT JOIN qryAuditEx ON tblAudit.CombField = qryAuditEx.CombField 
 SET tblAudit.Archived = "Yes"
WHERE (((qryAuditEx.CombField) Is Null));

Try something like this instead:

UPDATE tblAudit
SET tblAudit.Archived = "Yes"
WHERE tblAudit.CombField Not IN(SELECT CombField FROM qryAuditEx WHERE CombField Is Not Null);

Backup you data first.