RE: combine data from 2 tables in access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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




"MikeT" wrote:

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

Relevant Pages

  • Re: Howto embed select statements into other select statements
    ... I want to calculate the fraction of two counts and I have created the ... by id) table1, ...
    (comp.databases)
  • Howto embed select statements into other select statements
    ... I want to calculate the fraction of two counts and I have created the ... by id) table1, ...
    (comp.databases)
  • Re: update query
    ... will match any string which starts with a and ends with c. ... Probably because you have an extra blank between table1. ... Is there in fact a field named Field2 in your query? ... SET Field1 = Field2 ...
    (microsoft.public.access.queries)
  • Data type of a tuple id in r3?
    ... delete from table1 ... The data in field1 and field2 are ... How I finally figured out I needed to cast the tid was when I did a ...
    (comp.databases.ingres)
  • Re: Cycle Through two Tables
    ... INSERT INTO Table1 ... SELECT Field2 FROM Table2 ... (SELECT Field1 FROM Table1) ... "Running Microsoft Access 2000" ...
    (microsoft.public.access.queries)