Operation must be an updateable query (1 Viewer)

Gismo

Registered User.
Local time
Today, 03:33
Joined
Jun 12, 2017
Messages
1,298
Hi all,

Below code gives errors

The Standard Inspection DAW Qry - Combined is a union query
Could this be the reason for the error?

UPDATE DISTINCTROW ([Standard Inspection DAW Qry - Combined] INNER JOIN ((Operations INNER JOIN Control ON Operations.Control = Control.[Control Description]) INNER JOIN [DAW Sheet Temp File] ON Operations.Operation = [DAW Sheet Temp File].Operation) ON [Standard Inspection DAW Qry - Combined].[Standard Inspection] = [DAW Sheet Temp File].[Standard Inspection]) INNER JOIN [Aircraft List] ON ([Standard Inspection DAW Qry - Combined].Type = [Aircraft List].Airframe) AND ([Standard Inspection DAW Qry - Combined].Series = [Aircraft List].Series) SET [DAW Sheet Temp File].[Defect / Additional Work] = [DAW Sheet Temp File]![Standard Inspection] & Chr(13) & Chr(10) & [Standard Inspection DAW Qry - Combined]![Description], [DAW Sheet Temp File].[Mechanical Mhrs] = [Standard Inspection DAW Qry - Combined]![Hours], [DAW Sheet Temp File].[External Service Required] = [Control]![Control], [DAW Sheet Temp File].[Recommended Vendor] = [Operations]![Vendor]
WHERE ((([Aircraft List].[Aircraft Registration])=[Forms]![DAW Sheet - Main]![Registration]));
1649413960680.png
 

June7

AWF VIP
Local time
Yesterday, 17:33
Joined
Mar 9, 2014
Messages
5,423
Yes. Any update including a non-updatable query will not work.
 

Gismo

Registered User.
Local time
Today, 03:33
Joined
Jun 12, 2017
Messages
1,298
Yes. Any update involving a non-updatable query anywhere will not work.
Even though the union query is not the table being updated?
 

June7

AWF VIP
Local time
Yesterday, 17:33
Joined
Mar 9, 2014
Messages
5,423
Correct. Options are VBA, DLookup domain aggregate function, saving UNION dataset to a 'temp' table.

Resorting to UNION is indication of non-normalized data structure.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:33
Joined
Feb 28, 2001
Messages
27,001
Even though the union query is not the table being updated?

ANY non-updateable element makes the whole query non-updateable. The problem is often one of ambiguity. When you are about to update a field, Access can allow NO ambiguity in which field is to be updated. I.e. the update must have a non-ambiguous destination, a specific field in a specific record for each specific update.

Having multiple JOINs (due to multiple relationships) most frequently runs across the problem when the JOIN allows more than one field (in more than one combined - i.e. JOINed - record) to be selected for update due to not having a constraint (WHERE-style constraint) on one of the component tables in a multi-level JOIN query.

I.e. A JOIN B JOIN C, and you want to update a field in C but there are multiple potentially matching "paths" through records in B to lead to the same record in C. It would also work if updating a field in B where even though THAT part is unique, there are multiple possible matching records in C and you don't specify them.

Remember that for a JOIN, each COMBINATION of component records is itself a record. The fact that an individual table might be uniquely specifiable does not matter if you don't specify that part of the selection. The workaround is to figure out how to make the combination of contributing table elements completely selected.

I can't tell from what you posted exactly where this problem lies, but consider this: Your big query involves 5 tables but your query design grid only shows you using two of them. If ANY of the other three tables can have multiple matches (when not constrained) then there is your culprit.

I hope I was able to describe that clearly enough.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:33
Joined
Feb 19, 2002
Messages
42,981
Even without the union, you would have trouble. Your relationships are not correct. Your joins are not FK to PK.
 
Last edited:

Users who are viewing this thread

Top Bottom