Ms-access – Access 2013 update rows from multiple rows

ms access

I need help to construct an update query to populate MynewField with contents from multiple fields and rows where the MapID is the same.

RowID---MapID-----Extension-----  Name------    MynewField  
7        318        #222                         [#222Tom]   
22       318                       Tom           [#222Tom]

Best Answer

You can't write a single query to do that in Access but a 'make table query' and a separate 'update query' combo will work. Change to suit your needs.

    select MyTable.RowID
        ,[t1].[MaxOfExtension] & [t1].[MaxOfName] as NewValue
    into t2
    from MyTable
    inner join (
        select MyTable.MapID
            ,Max(MyTable.Extension) as MaxOfExtension
            ,Max(MyTable.name) as MaxOfName
        from MyTable
        group by MyTable.MapID
        ) as t1
        on MyTable.MapID = t1.MapID;

    update MyTable
    inner join t2
        on MyTable.RowID = t2.RowID
    set MyTable.MyNewField = [t2].[NewValue];
Related Question