"Operation Must Use An Updateable Query"

[jaaret]

I'm attempting to write an update query to flag phone numbers in a Prospects table when there's a matching number in a DoNotCall table. I've created a query with a 1-1 join between the two tables, joining on the phone number field (a text field). A select query correctly returns five matches, but when I change the query to an Update query and attempt to change the Prospect table phone number field to "Do Not Call" I receive the error message, "Operation Must Use An Updateable Query." What am I doing incorrectly?

 

[misscrf]

If you are updating the phone number field with the do not call field and lets say the phone number was 555-555-1212, and you are updating it to do not call... is that right?

I would check both fields in respective tables and make sure the field length of each is the same, and the rest of their properties. I would split the two tables in design view, vertically.

Then you can compare left to right, and just make sure which tables field you want to make reflect the other, and that the data in that field can handle the change. good idea to make a copy backup of the table if you want to be safe.

If you are making a different kind of update, please explain more. I love making a query work right!
misscrf

 

[jaaret]

I think I answered my own question. My original update query wasn't actually between two tables, it was between the Prospect table and a QUERY based on the DoNotCall table. (The query "cleans up" the DoNotCall table data.) If I change the query to a Make Table query and then create an update query between the Prospects table and the clean "DoNotCall" table the query updates correctly.

Can Update queries ever run when there is a join between a table and query or do tables always have to be used?

Darrell
In beautiful, sunny Seattle

 

[misscrf]

I think they can work, as long as the update feeds to a table. ie. you can't update a query. Does that make sense?

misscrf

in gloomy, rainy rochester

 

[jaaret]

The query that was linked to the Prospects table used the Group aggregate function to resolve duplicate phone numbers. I can now see why an Update query based on this underlying query would return a "not updateable" error since aggregate function queries are not updateable. I was trying to update the data in the Table, not the query, but I think Access may return a "not updateable" error on queries built on underlying not updateable queries.

Anyway I have it working the way I need it to. Thanks for your help!

Darrell