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.
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