Access Db Update One Table with Value from Another

Access DB update one table with value from another

You have to use a join

UPDATE inventoryDetails 
INNER JOIN BOM_TEMPLATES ON inventoryDetails.modelNumber = BOM_TEMPLATES.bomModelNumber 
SET inventoryDetails.itemDesc = [bomItemDesc];

Access: UPDATE column with values from another table

You need to join your tables, and then you can set the value:

UPDATE table1 
  INNER JOIN table2 ON table1.ID = table2.ID
SET table1.column1 = table2.column2
WHERE table2.ID = 1    -- not sure if you actually want to keep this criterium

Updating values in ms-access table with values from another table

Consider an INNER JOIN in update query:

UPDATE Table1 t1 
INNER JOIN Table2 t2 ON t1.ID = t2.ID
SET 
   t1.Col1 = t2.Col1,
   t1.Col2 = t2.Col2,
   t1.Col3 = t2.Col3,
   ...

However, to write the SET lines for 50 fields is a bit tedious and as you found out not easily done with the Design View. Hence, consider creating the query programmatically with VBA using a querydef, iterating through a tabledef's fields:

Public Sub CreateUpdateQuery()
    Dim tbl As DAO.TableDef
    Dim qdf As DAO.querydef
    Dim fld As DAO.Field
    Dim strSQL As String

    strSQL = "UPDATE Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID SET "

    For Each tbl In CurrentDb.TableDefs
        If tbl.Name = "Table1" Then
            For Each fld In tbl.Fields
                If fld.Name <> "ID" Then
                    strSQL = strSQL & " t1.[" & fld.Name & "] = t2.[" & fld.Name & "],"
                End If
            Next fld
        End If
    Next tbl

    strSQL = Left(strSQL, Len(strSQL) - 1) & ";"                 ' REPLACING LAST COLUMN

    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name = "QryName" Then
            CurrentDb.Execute "DROP TABLE " & qdf.Name           ' DELETES QUERY IF EXISTS
        End If
    Next qdf

    Set qdf = CurrentDb.CreateQueryDef("QryName", strSQL)  ' SAVES A STORED QUERY

    Set tbl = Nothing
    Set qdf = Nothing
End Sub

SQL Update from one table to another

Try

UPDATE tbl1
SET userid = (SELECT userid
FROM tbl2)

Update query using a field from another Access database

You can use a subquery to work with the IN clause in updates. That way, it's explicit which table is in which database.

UPDATE Table1 INNER JOIN (SELECT * FROM Table2 IN "C:\Folder\Database.accdb") t2 ON Table1.ID=t2.ID 
SET Table1.Field1 = t2.Field2

---

Related Topics