Update a row with max effective date

Question

Hi,

I am using this below query to update App table from Details table in MS Access. The code column will have multiple rows in Details table. I need to update the Group column and this query works perfectly.

On top of this I need to pick the maximum effective date row value for L2 Group from details table. Details table has a column called EFF_DT with date entries.

Can anyone help me with this.

UPDATE [App] INNER JOIN Details ON (Details.Code)=([AppInv].[Code]) SET [App].[Group] = IIF((Details.Group) IS NULL OR (Details.Group) = '', 'N/A', (Details.Group));