Access Update with Join Problem

[saascuba]

Can anyone tell me what's wrong with the following query:



UPDATE Equip_Info
INNER JOIN Clean_Data
ON Equip_Info.Equip_No = Clean_Data.Equip_No
SET Equip_Info.Next_Clean_Date = (SELECT MAX(Date_Time) FROM Clean_Data WHERE Equip_No='10001')
where Equip_Info.Equip_No = '1001'

when running it in the MS Query tool it returns

"Operation must use updateable query"

The subquery works and only gives one value so I am not sure how to proceed.

Thanks for your help

[ajetrumpet]

more than likely it is because you have an aggregate function in the query. that is one stopper for updating recordsets. you can't do it.

you cannot update a set when there is an aggregate function present.

[NoellaG]

Hi saascuba,

try changing (SELECT MAX(Date_Time) FROM Clean_Data WHERE Equip_No='10001') in:

Dmax("Date_Time","Clean_Data","Equip_No = '10001'")

gr
NG

[saascuba]

Thanks NoellaG, the dmax.... etc. worked.