If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > operation must use an updatable query

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Oct 2003
Posts: 61
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!
Reply With Quote
  #2 (permalink)  
Grand Poobah
Join Date: Sep 2003
Location: MI
Posts: 3,713
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 ...
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On