0

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

1 Answer 1

0

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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