you need to run 2 queries.
one the insert query, to insert new LotID (if it does not exists, if it already exists don't worry it
wont get inserted again, as long as you create the Index on LotID Without Duplicate).
then you run the Update query.
need delete Itemexists otherwise the sum will be wrong.
i am using below 4 steps
complicated but work.
whould you please help to simplify?
Private Sub Command1_Click()
'1..Markdown Itemexists(Itemexists = Yes in TempTable).
CurrentDb.Execute "UPDATE TempTable LEFT JOIN Table1 ON (TempTable.InDate = Table1.InDate) AND (TempTable.LotID = Table1.LotID) SET TempTable.Itemexists = Yes WHERE Table1.LotID Is Not Null AND Table1.QTY Is Not Null"
'2..INSERT INTO Table1 that items notexists
CurrentDb.Execute "INSERT INTO Table1 ( LotID, InDate, QTY ) SELECT TempTable.LotID, TempTable.InDate, TempTable.QTY FROM TempTable WHERE TempTable.Itemexists=No"
'3..Delete items notexists(after INSERT left Itemexists for sumup )
CurrentDb.Execute "DELETE TempTable.*, TempTable.Itemexists FROM TempTable WHERE TempTable.Itemexists =No"
'4..Sumup Itemexists(if any)
If DCount("*", "temptable") > 0 Then
Dim rst As Object
Dim rst1 As Object
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "temptable", , adOpenKeyset, adLockOptimistic
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.Open "table1", , adOpenKeyset, adLockOptimistic
rst.MoveFirst
Do Until rst.EOF
rst1.MoveFirst
rst1.Filter = "LotID = " & rst!LotID & " AND InDate = #" & rst!InDate & "#"
rst1!qty = Nz(rst!qty, 0) + Nz(rst1!qty, 0)
rst1.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
rst1.Close
Set rst1 = Nothing
End If
MsgBox "Complet"
End Sub
