combine data from 2 tables in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables in a database. Both tables have 1 common field 'Fax'. The
information in each of the tables is different.

I would like to combine the 2 tables into 1 table. Where there are
duplicates in the 'Fax' field, I would like to combine the information from
both tables in one record.

Can anyone suggest how I should go about this?
 
Example:

Source Table: Table1
Fields: Fax, Field1, Field2

Source Table: Table2
Fields: Fax, Field1, Field2

Destination Table: DestTable
Fields: Fax, Table1, Table2

INSERT INTO (Fax, Table1, Table2)
SELECT Table1.Fax, trim(nz(Table1.Field1) & " " & nz(Table1.Field2)),
trim(nz(Table2.Field1) & " " & nz(Table2.Field2))
FROM Table1 INNER JOIN Table2 ON Table1.Fax=Table2.Fax

You can also use left JOIN if not both tables contain a FAX. Please bear in
mind that then the FROM Table1 should be the table with the most FAX ids and
it would become then : FROM Table1 LEFT JOIN Table2 ON Table1.Fax=Table2.Fax

If you have more Rows for the same FAX then you should do everything
yourself in VBA by using a recordset.
Step 1: Create a new table with FAX field set to unique
Step 2: Insert all fax numbers from both tables
Step 3: then do something like see below
Step4: run code again for 2nd table

- Raoul

Dim rsDest As New ADODB.Recordset
Dim rsSrc As New ADODB.Recordset
Dim strTemp As String

rsDest.Open "NewTable", CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
rsSrc.Open "Table1", CurrentProject.Connection, adOpenDynamic,
adLockReadOnly
While Not rsDest.EOF
rsSrc.Filter = "Fax='" & rsDest("Fax")
strTemp = ""
While Not rsSrc.EOF
strTemp = strTemp & " " & Nz(rsSrc("Field1"))
strTemp = strTemp & " " & Nz(rsSrc("Field2"))
rsSrc.MoveNext
Wend
rsDest("Table1") = Trim(strTemp)
rsDest.Update
rsDest.MoveNext
Wend
rsDest.Close
rsSrc.Close
Set rsDest = Nothing
Set rsSrc = Nothing
 
Back
Top