November 28, 2014 at 12:44 am
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));
November 28, 2014 at 7:26 am
We're going to need a few more details (pardon the terrible pun) in order to assist you on this. First of all, if you have a one-to-many relationship between records in App and records in Details, then your query will process all of the records in Details in a more or less random fashion and leave the corresponding record in App with the value or lack thereof of the last record processed. Is that really what you want? Second, this sort of thing is more typically done on a report or table, as the data is typically time sensitive, and storing a calculated value in a table is generally not recommended.
Regarding your issue with the maximum effective date, that can typically be done with a GroupBy query where you select the Details record with the maximum date using the Max option for the effective date column, and GroupBy for the remaining columns. If your intent is to then do the update of the App.Group column with the Details.Group column of the record which has the maximum effective date, then you could do that by joining on the GroupBy query. Another option would be to use a subquery, but unless you are comfortable working with nested queries, the joining operation is easier to visualize.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply