Non-updatable query due to aggregates (max) (1 Viewer)

Hi,

I'm stumped on this & have been trying to figure it out today. I have two simple tables:

tbl_cost - contains the cost history for a series of items & the dates when that cost goes active

tbl_detail - 2nd table which contains the items number and blank fields for date & cost

I'm looking for a way to run an update query which would bring the max of the date field from tbl_cost into the appropriate items in tbl_detail. I'm struggling with figuring out how to do this since using the Group By/Max(date) in Access turns the query non-updatable. I think I saw somewhere that this can be worked around using SQL sub-queries however I can't get that to work. Help?

Try the following:

ByteMyzer said:

Try the following:

Code:Copy to clipboard

UPDATE tbl_detail AS T1
INNER JOIN tbl_cost AS T2 ON T1.item=T2.item
SET T1.date=T2.date, T1.cost=T2.cost
WHERE T2.date=
 (SELECT MAX(T3.date)
  FROM tbl_cost AS T3
  WHERE T3.item=T2.item
 )
;

Click to expand...

Thanks, ByteMyzer - I don't know about the original poster, but I myself found this info very helpful.

Perfect, thanks!