Don't understand why I get "Operation must use an updatable query"

[KHerm]

I'm updating two date columns in table1. I wrote an update query for one column that worked fine joining on table2. The same logic was used for the second column in the next query with table3 and I get "operation must use an updatable query". Access help says "This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (total) was calculated for the field (using either the Totals row or a domain function in the Field row)." Not much help since I'm updating a date after joining on an ID number and date. Why does the update work when updating from table2 and not from table3? The only difference, really is the table name and the column name.

The update query looks like this in SQL:

UPDATE TABLE1,TABLE3
SET TABLE1.ExpDate = [TABLE3].[exp_date]
WHERE TABLE1.ID_NUM = TABLE3.ID_NUM
AND TABLE1.ST_DATE = TABLE3.ST_DATE;

That logic works with TABLE1 AND TABLE2.

Thanks in advance!

Ken

 

[RickSpr]

Do you have permission to update TABLE3? I think you can only update a query if you can update all the tables in it.

Did you draw a join line between TABLE1.ID_NUM and TABLE3.ID_NUM? If you didn't, you're using a standard SQL join, rather than an Access INNER JOIN phrase in the SQL statement. Access doesn't allow updating unless you use INNER JOIN/draw a join line.

Does either TABLE1.ID_NUM or TABLE3.ID_NUM have a unique index? One of them must be unique, or your join will be many-to-many, which is not updatable.

You may also want to check the help file topic "When can I update data from a query" (Access 97). You can find it under the topic "Troubleshoot queries". Rick Sprague

 

[KHerm]

Thanks for the reply, Rick. I created all the tables involved, so I know there wasn't a permission problem. I checked for the one-to-many issue and it didn't exist either. I did work around the problem, though. I turned my one query into a table (instead of using it like a view) and it worked fine. I don't see what difference it should make, but I always say if it ain't broke don't fix it. (Or in this case, if it works now, leave it alone.)

Ken