dm1988 dm1988 - 1 month ago 13
SQL Question

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

I've got an Access 2010 Database with two tables T1 and T2.
I want to write an update query so that values in T1 get updated with values from T2. T2 got additional fields, which T1 does not have. Those fields should be ignored for the update. Except for those fields, T1 and T2 share the same fields and fieldnames.

Since the number of fields is quite big (at least 50), i was trying to look for a simple drag & drop configuration of the query. I connected both tables in the design view by dragging the primary key of T1 to the primary key of the T2. Then i added all fields of T1 to the datagrid below the table view.

Access now wants me to select and input each and every field ([T2].[fieldname]) below the added fields. Want i try to achieve is that those information get filled in automatically. I doubt, that Access lacks such a essential functionality and would be thankful for someone to point me in the right direction or give some insight on why this is NOT a proper way to update my table.

Answer

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
Comments