Update query problem (1 Viewer)

I know this is long but please bear with me, I need some help.

I am having difficulty updating a value in a table via a series of queries. I am getting the following error: Operation must use an updatable query. (Error 3073)

I have three tables involved, the main table “tblMasterNeeds” supplies criteria, a table joined to the main table by an ID number “tblExistingSegVC” contains the field that must be updated, table “tblCMSforTransGIS” is the data source table and is not linked to the other tables.

tblMasterNeeds contains these fields; ID, HwyNum, BMP, EMP.
tblExistingSegVC contains these fields; ID, HighV/C.
tblCMSforTransGIS contains these fields; HwyNum, BMP, EMP, V/C.

I am using data from three fields; HwyNum, BMP, and EMP in the main table as query criteria to pull data from the data source table, since the data source table does not contain the ID field I am using this query to establish that association.

I then query that query using Max in the Totals row of the V/C field to filter the data down to the record with the maximum value. I now have the highest V/C from the source table associated with the ID field.

I then run an update query that attempts to put the highest V/C in the HighV/C field of table tblExistingSegVC. The two tables are joined in the query by the ID number.

I get the following error Operation must use an updatable query. Which is (Error 3073).

I have accomplished the task by using a make table query to temporarily hold the data and then ran an update query to update tblExistingSegVC from that table but that is clunky and I would like to be able to update the records using queries, code eventually. Since I am able to update the records in this manner I know it is not a data access or permissions issue.

I tried changing the join type but that doesn’t fix it.

Any ideas?

This errors usually occurs when Access thinks you have duplicate occurances of the record you're trying to update within the query (i.e. the same values get repeated in the query as a result of a join).

One thing to try (and do check that the results are OK, if it lets you do it) is to change one of the properties of the SELECT query. Open it in design view and open the properties window. Click on the background of the query area to tie the properties window to the query itself, rather than the field that is initially selected. Then change the property "Recordset Type" which is almost certainly currently set to "Dynaset" to "Dynaset (Inconsistent Updates)".

See if that solves it.

Simon.

Queries that include aggregate functions such as Max() are NOT updateable. You need to rework the query so that the Max() value is retrieved via a sub-select. That will probably fix the problem. If that doesn't work, you can break the query so that you find the Max() and do a make-table. Then use that temp table in the join to bring in the Max() value.

Pat Hartman said:

Queries that include aggregate functions such as Max() are NOT updateable. You need to rework the query so that the Max() value is retrieved via a sub-select. That will probably fix the problem. If that doesn't work, you can break the query so that you find the Max() and do a make-table. Then use that temp table in the join to bring in the Max() value.

Click to expand...

Hi: Pat, thanks for your suggestion.

The Select query looks like this:
SELECT a.MB_RECNUM,
Sum(...) AS ProblemPoints,
Sum(...) AS DatePoints
FROM b INNER JOIN a ON b.MAINTENANCE_ID = a.MAINT_ID
GROUP BY a.MB_RECNUM;

Can you give me some ideas how to make a changes so the Sum() value retrieved via a sub-select? Should I do in the Select query or Update query. (Sample code will be great!)

Thank you.