Solved: Insert data from one table into another

[mud2]

I have two tables, TableA and TableB with same structure. Each may/may not contain the same data. I want to add (append) to TableA Those records in TableB whose data in a chosen column is not equal to those in the same column in TableA.

It would seem that a "simple" SQL should do it..but not for me!

[xCav8r]

Do both these tables have primary keys?

[mud2]

To XCav8r: Neither table has a primary key. ???

[xCav8r]

There are a few ways you could go about this. You might try the following steps:

1. Use an append query to add the data from TableB to TableA. (Or create a new table with a make table query using TableA, then use TableB in the append query to add its data.)
2. Use a SELECT DISTINCT query to get unique values.
3. Use the SELECT DISTINCT query to make the desired table.

Another method:

1. Create a SELECT query for each table.
2. Use a UNION query to join the unique values.
3. Use the UNION query to make the desired table.

SELECT * FROM TableA
UNION
SELECT * FROM TableB

[mud2]

Looks like I marked "Solved" too soon, but hetre is something that works.

And it is MUCH more complicated than that suggested by Cav8r:
First, append one table to the other, then


[VBA] Public Sub Sweep1(ByVal Table As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Dup1 As String
Dim Dup2 As String
Dim Dup As String
Dim index1 As Integer
Dim index2 As Integer
Dim TableSize As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset(Table)
TableSize = rst.RecordCount
With rst
For index2 = 1 To TableSize
index1 = 1
.MoveFirst
Do Until .EOF
Dup = ![Key]
If (index1 = index2) Then
Dup1 = Dup
Else
Dup2 = Dup
End If
If ((Dup1 = Dup2) And (index1 > index2)) Then
.Delete
End If
.MoveNext
index1 = index1 + 1
Loop
Next index2
.Close
End With
End Sub
[/VBA]

[Norie]

mud

Do you really need code for this?

From the code it certainly looks like there are some linked fields between the tables.

Perhaps you can give more information on the table structure, also what/where should records be appended and according to what criteria.
Those fields might not have a defined relationship, but you could create one in a query.

[xCav8r]

Yeah, it's rather tough to help without the specifics.

PS. Mud, I think the VBA tag has to be enclosed by square brackets, ie., [VBA] and [/VBA]