Last post Dec 04, 2014 02:30 AM by Kevin Shen - MSFT
Nov 28, 2014 01:39 AM|vignesht5|LINK
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].[Code]) SET [App].[Group] = IIF((Details.Group) IS NULL OR (Details.Group) = '', 'N/A', (Details.Group));
Nov 28, 2014 02:02 AM|oned_gk|LINK
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].[Code]) ;
Nov 28, 2014 02:12 AM|vignesht5|LINK
I need to pick the maximum effective date row value from details table and curious know how that can be mentioned.
Dec 04, 2014 02:30 AM|Kevin Shen - MSFT|LINK
For your issue ,I suggest that you can try the code below:
SELECT Details.ID, Max(Details.DetailDate) AS MaxOfDate
GROUP BY Details.ID;