Match data in two tables and update or add

[FranS37]

I have to do something that I'm stumbling on.

I have two tables with matching fields and some matching data.

Two of the fields are box and ID. There are many id's for each box in the tables.

I have to search on box field in table A, then go look for that box in table B and if that box is not in table B, append the Box and all its IDs to table B. If the box is in Table B, then match on Box and ID and update all other fields.

This is where I get lost. If Table A has more records for a Box(combination of Box and ID) I have to append those to Table B.

My code seems convoluted and I'm wondering (if I've made this clear) if anyone can help me with a structure to do something like this.

Any help is appreciated.

 

[PHV]

I'm afraid you need 2 queries:
UPDATE TableB B INNER JOIN TableA A ON B.Box = A.Box AND B.ID = A.ID
SET B.data1 = A.data1, B.data2 = A.data2;

INSERT INTO TableB B (Box, ID, data1, data2)
SELECT A.Box, A.ID, A.data1, A.data2
FROM TableA A LEFT JOIN TableB B ON A.Box = B.Box AND A.ID = B.ID
WHERE B.ID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cloonalt]

Thanks, PH. That works for me.

It's running very slow, however.

For the update I'm using the below code and similar for the append.

oRs.Open "Select * from TableA", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
DoCmd.SetWarnings True
DoCmd.Hourglass True

With oRs
While Not .EOF
If oRs.Fields("box_Num") = Me.txtBox_num Then

strSQL = "UPDATE ...

Set cmdUpdate = New ADODB.Command
With cmdUpdate
.ActiveConnection = oConn
.CommandType = adCmdText
.CommandText = strSQL
.Execute
End With
End If

.MoveNext
Wend

Can I be more efficient than this? Thanks for your help.

 

[PHV]

Can I be more efficient than this?
Why not using the action queries I suggested you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cloonalt]

Yes, I tried that also. It's still very slow.

I indexed the fields that I'm joining on in the table that I'm appending to, but I can't change the indexes on the tables that I'm appending from.

Thanks for your reply