Update queary using Max

[roostermt]

I need some help.

I need to update a date field in one table from data in onther.

Using the total query (JET) and the max setting I can get the data I need.
But the JET does not allow me to update.

I've tried using Dmax but I must be doing something wrong as I don't get all
the data.

Any help would be great:

Here is the current Query that works using totals (JET).

SELECT [Station tapes out].station, [Station tapes out].[PI CODE],
tblShowList.Title, [Station tapes out].[800 number], [Station tapes out].
[last contact], [Station tapes out].[Contact Comment], Max(tblStationSales.
[Date sold]) AS [MaxOfDate sold]
FROM ([Station tapes out] LEFT JOIN tblStationSales ON ([Station tapes out].
[PI CODE] = tblStationSales.[PI Code]) AND ([Station tapes out].station =
tblStationSales.Station)) INNER JOIN tblShowList ON [Station tapes out].[PI
CODE] = tblShowList.[PI Code]
GROUP BY [Station tapes out].station, [Station tapes out].[PI CODE],
tblShowList.Title, [Station tapes out].[800 number], [Station tapes out].
[last contact], [Station tapes out].[Contact Comment], [Station tapes out].
[date sent], [Station tapes out].[PI CODE], [Station tapes out].Active,
tblShowList.Active
HAVING ((([Station tapes out].[last contact])<Date()-21 Or ([Station tapes
out].[last contact]) Is Null) AND ((Max(tblStationSales.[Date sold]))<Date()-
21 Or (Max(tblStationSales.[Date sold])) Is Null) AND (([Station tapes out].
[date sent])<Date()-21) AND (([Station tapes out].Active)=Yes) AND (
(tblShowList.Active)=Yes));

 

[Tom van Stiphout]

I'm a bit confused by your question. You are showing a SELECT query
yet you want to UPDATE.
Perhaps a simple example using the Northwind sample application will
help.
This does not work:
UPDATE Orders SET Orders.[Shipped Date] = (select Max([Shipped Date])
from Orders)
WHERE (((Orders.[Shipped Date]) Is Not Null));

This works:
UPDATE Orders SET Orders.[Shipped Date] = DMax("[Shipped
Date]","Orders")
WHERE (((Orders.[Shipped Date]) Is Not Null));

Or perhaps I misunderstood and you simply bound this query to a form
or used it in a recordset and you are expecting to be able to update
some columns. Alas, *all* totals queries are not updatable. And if you
think about it, it's for good reasons.

-Tom.
Microsoft Access MVP