CODEWITHSUNDEEP
sqlms-access
dm1988
dm1988

Reputation: 87

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.

Upvotes: 0

Views: 2438

Answers (2)

Parfait
Parfait

Reputation: 107567

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

Upvotes: 2

Rominus
Rominus

Reputation: 1221

I'm not sure what the problem with your attempt is, but your goal should be straightforward using a coded update query, something like:

UPDATE T1
SET T1.col1 = T2.col1,
    T1.col2 = T2.col2,
    T1.col3 = T2.col3,
    [repeat for each field you're updating]
WHERE T1.pKey = T2.pKey

By only including the fields from T1 that you want to update, the extra fields in T2 don't matter.

Upvotes: 1

Related Questions