Update query using non - update query

[Costefran]

Can anyone help

I am trying to use a non - updatable query to filter records on a table and then update the resulting records of the table but I get the message "operation must use an updatable query"

I have posted the SQL below

UPDATE [Application Report CAP Query E Table 1] LEFT JOIN [Cost Authorisation Table] ON [Application Report CAP Query E Table 1].[Cost Auth ID] = [Cost Authorisation Table].[Cost Auth ID] SET [Cost Authorisation Table].[Application Approved Count] = 1;

Application Report CAP Query E Table 1 is the non - updateable query

Any help would be really appreciated

Thanks

 

[PHV]

Updating the right part of a left join makes no sense for me ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Costefran]

Thanks

Looks like I havn't explained the problem very well

Perhaps another approach

The left join query is the record source for a subform from table A which displays say 10 records, each with a unique ID

I would like to update a field called "count" on each of the selected records of Table A but the subform records are not updatable
so I am trying to use the same query to find records in table A to update

I suppose I could do it another way by referring to the unique ID of all records on the subform but when I have tried this sort of thing before it only selects the first record on the subform

Hope this explains it as my grey matter is quickly disintegrating
Thanks

 

[Guiderman]

I was getting the same error and I am doing just a simple query with no joins.

It turns out the problem is not with the query but the linked table. The table I am updating thru Access is missing the primary key.

Restored the key and the error is gone.

 

[fneily]

Have you seen:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp