update query (Max(Date))

[prettitoni]

Can someone help me write an UPDATE query that sets the Date of Table1 equal to the Max(Date) in Table2 where the tables are joined on Table1.ID = Table2.ID?

 

[GHolden]

Hi,

If I get you right, you want to update a date field in Table1 with the Maximum date from Table2 where Table1.ID = Table2.ID

If that's true the following should help...

UPDATE Table1 
SET DateField = 
   (SELECT Max(DateField) AS MaxDate 
    FROM Table2
    WHERE Table1.ID = Table2.ID)

As this is an UPDATE I'd test on a copy of the data to ensure it's what you want.


There are two ways to write error-free programs; only the third one works.

 

[prettitoni]

That gives me "Operation must use an updateable query

 

[GHolden]

Are you using Access 97?

There are two ways to write error-free programs; only the third one works.

 

[prettitoni]

No...Access 2002.

 

[GHolden]

Stumped....

I know this is a problem in Access 97 (using agregates in updates) but it's not listed as a problem in Access 2002 on MS (haven't got a copy of Access 2002 to test with though). However I've tested this in SQL Server and it works fine...


There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Give this a go...

UPDATE Table1
SET DateField = 
(DMAX("DateField","Table2","ID = " & Table1.ID))

There are two ways to write error-free programs; only the third one works.

 

[prettitoni]

Okay I tried that. The query ran but it updated all the Table1.Datefield records with blank data.

 

[PHV]

And something like this ?
UPDATE Table1 INNER JOIN
(SELECT Table2.ID, Max(DateField) AS MaxDate FROM Table2
GROUP BY Table2.ID) B ON Table1.ID = B.ID
SET DateField = B.MaxDate
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[prettitoni]

Operation must use an updateable query again :-(