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]
  • I tried UPDATE [MyTable] SET [MyTable].[Mynewfield] = [Extension] & [Name] but results are contrained to one row. – usnco Sep 16 '15 at 11:23

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];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.