How to merge two database tables when only some fields are common?

Question

In MS Access I have two tables (A and B), and the task is to insert B into A. However, there are some special conditions:

All fields are of type text.
A and B have a some common fields.
The same key field is guaranteed to exist in both, and its values to be always different.
A has some fields that B does not have. The inserted records should have those fields blank.
B has some fields that A does not have. These fields must be created in A, and the existing records in A should have them blank.
There are many cases like this one, so the query should not explicitly include the field names, since it would be tedious to personalize the query for each case. However, the key field is always named the same.
Creating a new table C instead of directly replacing A is acceptable.

Example:

Table A:
key  a       b       c
--- ------- ------- -------
k0  hello   dear    world
k1  bye     cruel   world

Table B:
key  a       d       e
--- ------- ------- -------
k2  welcome john    doe
k3  turulu  ann     harp

Table C (the new A):
key  a       b       c       d       e
--- ------- ------- ------- ------- -------
k0  hello   dear    world
k1  bye     cruel   world
k2  welcome                 john    doe
k3  turulu                  ann     harp

do you know *a priori* which are the fields that exist in both tables? Or only the "key" field is common to the tables? — Barranka, Sep 13 '13 at 21:19
@Barranka Because A is being constantly updated by the insertion of multiple B tables, the fields in A are constantly growing, so the common fields change as well. — apalopohapa, Sep 13 '13 at 21:22
@apalopohapa but the key field name is common to both tables, right? — Barranka, Sep 13 '13 at 21:41
Tedious is the only way to do this unfortunately...it's a very (VERY) poorly designed process made even worse by it being in Access. I'm guessing someone along the way implemented the idea of 1 table = 1 customer (or product) and this tedious suffering you are going through is the end result. Only answer I can give you here...address the process before it gets worse. — Twelfth, Sep 13 '13 at 21:47

score 1 · Accepted Answer · answered Sep 13 '13 at 21:53

Create an Access Module and use the following code. Replace the values in the test sub with your table and destination names

Option Compare Database
Option Explicit

Function SplatTablesSql(pT1 As String, pT2 As String, pDest As String)
    Dim lDb As Database
    Dim lTd1 As TableDef, lTd2 As TableDef
    Dim lField As Field, lF2 As Field
    Dim lS1 As String, lS2 As String, lSep As String

    SplatTablesSql = "Select "
    lS1 = "Select "
    lS2 = "Select "

    Set lDb = CurrentDb
    Set lTd1 = lDb.TableDefs(pT1)
    Set lTd2 = lDb.TableDefs(pT2)

    For Each lField In lTd1.Fields
        SplatTablesSql = SplatTablesSql & lSep & "x.[" & lField.Name & "]"
        lS1 = lS1 & lSep & "a.[" & lField.Name & "]"
        Set lF2 = Nothing
        On Error Resume Next
        Set lF2 = lTd2.Fields(lField.Name)
        On Error GoTo 0
        If lF2 Is Nothing Then
            lS2 = lS2 & lSep & "Null"
        Else
            lS2 = lS2 & lSep & "b.[" & lField.Name & "]"
        End If
        lSep = ", "
    Next

    For Each lField In lTd2.Fields
        Set lF2 = Nothing
        On Error Resume Next
        Set lF2 = lTd1.Fields(lField.Name)
        On Error GoTo 0
        If lF2 Is Nothing Then
            SplatTablesSql = SplatTablesSql & lSep & "x.[" & lField.Name & "]"
            lS1 = lS1 & lSep & "Null as [" & lField.Name & "]"
            lS2 = lS2 & lSep & "b.[" & lField.Name & "]"
        End If
        lSep = ", "
    Next

    SplatTablesSql = SplatTablesSql & " Into [" & pDest & "] From ( " & lS1 & " From [" & pT1 & "] a Union All " & lS2 & " From [" & pT2 & "] b ) x"
End Function

Sub Test()

    CurrentDb.Execute SplatTablesSql("a", "b", "c")

End Sub

score 1 · Answer 2 · edited May 23 '17 at 11:50

The easiest way I can think to solve this is to use VBA to create the query definition.

I will assume that there's a column named key which is common to both tables.

I found here that you can use collections to make a dictionary-like structure. I'll use that to build the field list.

So, here we go:

public function contains(col as Collection, key as variant) as boolean
    dim obj as variant
    on error goto err
        contains = True
        obj = col(key)
        exit function
    err:
        contains = false
end function

public sub create_this_query(tbl1 as String, tbl2 as String, keyField as String)
    ' tbl1 and tbl2 are the names of the tables you'll use
    dim db as DAO.database, rs1 as DAO.recordset, rs2 as DAO.recordset
    dim columns as Collection
    dim strSQL as String
    dim i as integer
    dim obj as variant, colName as String

    set db = currentdb()
    set tbl1 = db.openrecordset(tbl1, dbopendynaset, dbreadonly)
    set tbl2 = db.openrecordset(tbl2, dbopendynaset, dbreadonly)
    set columns = new Collection

    ' Let's create the field list (ommiting the keyField)
    for i = 1 to tbl1.fields.count
        if not contains(columns, tbl1.fields(i).Name) _
           and tbl1.fields(i).Name <> keyField then
            columns.add tbl1.fields(i).Name, tbl1.fields(i).Name
        end if
    next i
    for i = 1 to tbl2.fields.count
        if not contains(columns, tbl2.fields(i).Name) _
           and tbl2.fields(i).Name <> keyField then
            columns.add tbl1.fields(i).Name, 1 ' The value is just a placeholder
        end if
    next i
    ' Now let's build the SQL instruction
    strSQL = "select [a].[" & keyField & "]"
    for colName in columns
        strSQL = strSQL & ", [" & colName & "]"
    next obj
    strSQL = strSQL & " " & _
             "from " & _
             "    (" & _
             "        select [" & keyField & "] from [" & tbl1 & "] " & _
             "        union " & _
             "        select [" & keyField & "] from [" & tbl2 & "] " & _
             "    ) as a " & _
             "left join [" & tbl1 & "] as t1 " & _
             "    on a.[" & keyField & "] = t1.[" & keyField & "] " & _
             "left join [" & tbl2 & "] as t2 " & _
             "    on a.[" & keyField & "] = t2.[" & keyField & "] "
   ' Finally, let's create the query object
   db.createQueryDef("myNewQuery", strSQL)
end sub

Hope this helps

How to merge two database tables when only some fields are common?

2 Answers2