Warning: Operation must be an updatable query

[exceii]

Hey,

Can anyone see the problem with this update query.

This query is supposed to update Orders.CommissionID to a calculated maximum value from another table/query if the value of Orders.CommissionID is still its default value of 1.

UPDATE CommissionGroupIDGroupMax INNER JOIN Orders ON CommissionGroupIDGroupMax.SurveyorID = Orders.Surveyor SET Orders.CommissionID = [CommissionGroupIDGroupMax].[CommissionGroupIDMax]
WHERE (((Orders.CommissionID)=1));

CommissionGroupIDGroupMax is a query which finds the grouped maximum CommissionGroupID in the CommissionsGroups table.

SELECT CommissionGroups.SurveyorID, Max(CommissionGroups.CommissionGroupID) AS CommissionGroupIDMax
FROM CommissionGroups
GROUP BY CommissionGroups.SurveyorID;

I seem to be unable to construct any update query which relies on other table data

 

[exceii]

As it turns out, the Jet 4 engine does not let an update query run if the source data is the product of an aggregated function e.g. max().

That workaround is to create a temp table to store the data, join the table in the update query, then drop the table after the query has run.

Also fyi, Jet 4 will not let sub queries run in an update if the sub query acts as a join. For that you need to use a join. A bit unreasonable me thinks (especially when the error thrown is about as vague as can be).

 

[vbaInet]

Well, you shouldn't be saving calculated values and in your case you are not following best practices on two occasions - saving it to a temp table and saving it permanently.

 

[exceii]

Well, you shouldn't be saving calculated values and in your case you are not following best practices on two occasions - saving it to a temp table and saving it permanently.

That would be true for something like "Totals" but I'm using max() as more of a utility. That value of the max() function gets assigned to a field that is used for a join, so it is necessary.

 

[vbaInet]

You can still get the max of a field per group in most cases. It wouldn't require an UPDATE query like you've done, just get it on the fly.

NB: The normal workaround if it's absolutely necessary to save a derived value is to use one of the Domain aggregate functions, so in your case it would be DMax().

 

[exceii]

I still get the same error using DMax().

And I can't use DMax/Max() on the fly because the value will always be incrementing when a new CommissionGroupID is created (CommissionGroupID acts in the same way as an invoice but for paying commissions)and I need the particular value at that particular instant to assign it as a foreign key.

 

[vbaInet]

If the values aren't constant then I guess there's a need to update the table.

You said it failed, did you remove the INNER JOIN to your Max query?

 

[exceii]

Yep. Actually I haven't even added the join for the max query yet. I've been procrastinating lol.

 

[vbaInet]

I see your problem, you are still using the Max query for updating. So if you create a query to get the Max using DMax(), i.e.

DMax(per group) = [Field]

and under that you filter out only the max records per group by just putting True in the criteria row under the above.

Then you can convert this query to an UPDATE query.

 

[exceii]

I'm pretty much lost with your last post.

The query I used for the max values is:
SELECT CommissionGroups.SurveyorID, DMax("CommissionGroupID","CommissionGroups") AS CommissionGroupIDMax
FROM CommissionGroups
GROUP BY CommissionGroups.SurveyorID;

Its basically the same as the first query but I have replaces the Max() function with DMax().

 

[vbaInet]

You still have GROUP BY in there. Your SELECT statement should look like this:

SELECT SurveyorID, CommissionGroupID, DMax("CommissionGroupID","CommissionGroups", "SurveyorID = " & [SurveyorID]) AS CommissionGroupIDMax, (DMax("CommissionGroupID","CommissionGroups", "SurveyorID = " & [SurveyorID]) = [CommissionGroupID]) AS IsMax
FROM CommissionGroups
WHERE (DMax("CommissionGroupID","CommissionGroups", "SurveyorID = " & [SurveyorID]) = [CommissionGroupID]) = True

Then convert to an UPDATE query.