Update query for each id

[giannirusso]

Hello, sorry for my english.



Im a newbie and I need some help for my access database.

If I need to return the top date for each id that can have multiple dates, like this:

ID DATE
1 05/02/2005
1 06/03/2006
3 10/11/2004
3 11/12/2007

The code can be:

SELECT TOP 1 * FROM [table]
ORDER BY table.[Date] DESC;

And the result should be:

1 06/03/2006
3 11/12/2007


Now, if I would like to update the MAXDATE column in the source table with the top date, like this :

ID MAXDATE
1 06/03/2006
1 06/03/2006
3 11/12/2007
3 11/12/2007

How can I do it?
I need this for having a query based on a table who can be updated.

Thanks
Gianni

[ranman256]

create a make table query of the ID , MAX(date), so the results are in their own table.
then create an update table, join the temp table to the data table (on ID) to update the dates.