10-19-06, 15:01 #1Registered User
- Join Date
- Oct 2003
Unanswered: operation must use an updatable query
I keep running into this same problem every now and then and it is driving me nuts: I have a simple access database with two tables. I want to update a field in table number one with a field from table two. First however I have to do some aggregation on the data in tabel number two. I therefore build a query on table two to do the aggregation first. After that I build the update query. This update query contains table number one and of course the query with the aggregations of table number two. I can join the two on a certain key field. Now when I try to run the update query I get the above mentioned error message saying that the operation must use an updatable query.
I do not understand why this should be a problem?? The update should be performed on table number one (not on the key field) and not on the (aggregation) query or table 2. The only solution I have been able to come up with is turning the query doing the aggregation into a make table query and after that doing the update using the newly created table and table number one. This is really annoying me. Isn't there another way
So in short the problem seems to be that in an update query I cannot join a table with an aggregated query and update the tabel even though the query used in the join is a query on a completely different table.
Can anyone please help me with this because I am tired of making all these extra tables which in my opinion should not be necessary??Nobody dies a virgin because life screws us all!
10-19-06, 15:14 #2Grand Poobah
- Join Date
- Sep 2003
By definition it should NOT be updateable ... Think about it: you're aggregating values in a value THAT IS NOT UNIQUELY IDENTIFIABLE BECAUSE IT IS NOT PART OF ANY TABLE and as such your update query will not work because it REQUIRES JUST THAT ATTRIBUTE: A UNIQUE WAY TO IDENTIFY THE ROW TO UPDATE AND THE ROW FROM WHICH THE UPDATE GETS ITS VALUE. Now, this IS trivial using VBA ... Gen your query to get the result to update with. Gen the update query on the table and row with that value. Execute. Done.Back to Access ... ADO is not the way to go for speed ...