locked
Update a row with max effective date RRS feed

  • Question

  • User-1888080761 posted

    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 update the Group with max effective date from the 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].
    ) SET [App].[Group] = IIF((Details.Group) IS NULL 
    OR (Details.Group) = '', 'N/A', (Details.Group));

    Friday, November 28, 2014 1:39 AM

Answers

  • User1918509225 posted

    Hi vignesht5,

    For your issue ,I suggest that you can try the code below:

    SELECT Details.ID, Max(Details.DetailDate) AS MaxOfDate
    FROM Details
    GROUP BY Details.ID;

    Best Regards,

    Kevin Shen.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2014 2:30 AM

All replies

  • User-1716253493 posted

    Maybe like this

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

    Friday, November 28, 2014 2:02 AM
  • User-1888080761 posted

    Hi,

    I need to pick the maximum effective date row value from details table and curious know how that can be mentioned.

    Friday, November 28, 2014 2:12 AM
  • User1918509225 posted

    Hi vignesht5,

    For your issue ,I suggest that you can try the code below:

    SELECT Details.ID, Max(Details.DetailDate) AS MaxOfDate
    FROM Details
    GROUP BY Details.ID;

    Best Regards,

    Kevin Shen.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2014 2:30 AM