Update query with Select statement

[mdmatiullah]

Table PR has multiple records on same primary key P.I .I want to update P.HMD with count of PR.I on specified condition . I am getting this error. Operation must be an updatable query.
Access query is given below.

UPDATE P INNER JOIN PR ON P.I=PR.I SET P.HMD = (SELECT COUNT(PR.I) FROM PR WHERE PR.T=119 AND PR.CD="Mk");

Thanks

 

[vbaInet]

Calculated values should not be saved. Just calculate it on-the-fly.

 

[gblack]

I think because you have the link and you are aggregating data (i.e., counts) you can't update the table within the query you made.

What you could do is change it into a make table query... then you'd have a table with the counts you wanted in a table and you could apply those to your table "P" afterwards.

Something like this:

SELECT I, COUNT(I) as HMD, "counts" as tblType INTO PRcounts
FROM PR 
WHERE T=119 AND CD="Mk";

There are a couple ways to apply that value to the aformentioned table (i.e., "P")

One would be to use a Select query... linking PRcount to P by I

Then making a "newHMD" field.

For example... newHMD: IIF(tblType = "counts", PRcounts.HMD, P.HMD)

There's a bunch of ways to skin this cat... this would be one.

 

[vbaInet]

What you could do is change it into a make table query... then you'd have a table with the counts you wanted in a table and you could apply those to your table "P" afterwards.

You don't need a Make Table query and it can be done in the same query but the point is calculated values should not be saved.

 

[gblack]

Yep, all three assertions are true.

I have just found that it often makes life easier to pull stuff into a new table vs. working with an aggregated query.

Whatever floats your boat.