Append query added duplicate.

[smtazulislam]

Hi, any help will appreciate.
I facing a little problem in append query make duplicate each hit in the button.

My appending query Like:

INSERT INTO tblTransactionWorkers ( EmployeeID, TransferDate, CompID, BranchID, BranchTypeID, WorkCategoryID, LeaveDate, Status )
SELECT tblTransactionWorkersIN.EmployeeID, tblTransactionWorkersIN.TransferDate, tblTransactionWorkersIN.CompID, tblTransactionWorkersIN.BranchID, tblTransactionWorkersIN.BranchTypeID, tblTransactionWorkersIN.WorkCategoryID, tblTransactionWorkersOUT.LeaveDate, IIF(NOT IsNull(tblTransactionWorkersOUT.LeaveDate), 'Transfer', 'Current') AS Status
FROM tblTransactionWorkersIN LEFT JOIN tblTransactionWorkersOUT ON tblTransactionWorkersIN.EmployeeID = tblTransactionWorkersOUT.EmployeeID;

Code :

Private Sub btnAppendData_Click()
    Dim lngPrevCount As Long
    Dim lngCurrCount As Long

On Error GoTo exit_sub
    
    DoCmd.SetWarnings False
    'count how many records before appending
    lngPrevCount = DCount("1", "tblTransactionWorkers")
    lngCurrCount = DCount("1", "tblTransactionWorkers")
    DoCmd.OpenQuery "qryTransactionWorkerDataAppend", acViewNormal, acEdit
  
 If lngPrevCount <> lngCurrCount Then

        MsgBox "Your append Record has been successfully saved." & vbCrLf & _
            "Appended " & lngCurrCount - lngPrevCount & " record(s)."
 Else
        MsgBox "No record were appended." & vbCrLf & _
            "All employees for the period: "  & _
            "already in tblTransactionWorkers."
    End If
    
    DoCmd.SetWarnings True
    
exit_sub:
    Me.Refresh

End Sub

 

[ebs17]

Because of duplicate avoidance: Include an inconsistency check in the query. I have assumed in the following proposal that EmployeeID is the only key for the duplicate assessment.

INSERT INTO
   tblTransactionWorkers(
      EmployeeID,
      TransferDate,
      CompID,
      BranchID,
      BranchTypeID,
      WorkCategoryID,
      LeaveDate,
      Status
   )
SELECT
   TIN.EmployeeID,
   TIN.TransferDate,
   TIN.CompID,
   TIN.BranchID,
   TIN.BranchTypeID,
   TIN.WorkCategoryID,
   TOUT.LeaveDate,
   IIF(TOUT.LeaveDate IS NULL, 'Current', 'Transfer') AS Status
FROM
   (tblTransactionWorkersIN AS TIN
      LEFT JOIN tblTransactionWorkersOUT AS TOUT
      ON TIN.EmployeeID = TOUT.EmployeeID
   )
   LEFT JOIN tblTransactionWorkers AS W
   ON TIN.EmployeeID = W.EmployeeID
WHERE
   W.EmployeeID IS NULL
;

Sufficient code

Dim db As DAO.Database

Set db = CurrentDb
db.Execute "qryTransactionWorkerDataAppend", dbFailOnError

MsgBox db.RecordsAffected & " Records are appended"

 

[smtazulislam]

Because of duplicate avoidance: Include an inconsistency check in the query. I have assumed in the following proposal that EmployeeID is the only key for the duplicate assessment.

INSERT INTO
   tblTransactionWorkers(
      EmployeeID,
      TransferDate,
      CompID,
      BranchID,
      BranchTypeID,
      WorkCategoryID,
      LeaveDate,
      Status
   )
SELECT
   TIN.EmployeeID,
   TIN.TransferDate,
   TIN.CompID,
   TIN.BranchID,
   TIN.BranchTypeID,
   TIN.WorkCategoryID,
   TOUT.LeaveDate,
   IIF(TOUT.LeaveDate IS NULL, 'Current', 'Transfer') AS Status
FROM
   (tblTransactionWorkersIN AS TIN
      LEFT JOIN tblTransactionWorkersOUT AS TOUT
      ON TIN.EmployeeID = TOUT.EmployeeID
   )
   LEFT JOIN tblTransactionWorkers AS W
   ON TIN.EmployeeID = W.EmployeeID
WHERE
   W.EmployeeID IS NULL
;

Sufficient code

Dim db As DAO.Database

Set db = CurrentDb
db.Execute "qryTransactionWorkerDataAppend", dbFailOnError

MsgBox db.RecordsAffected & " Records are appended"

thank you very much. Its worked.
tblTransactionWorkersIn recored is append. not insert duplicate.
but, if tblTransactionWorkersOUT table have any update like field 'Leavedate' , then append not upldate.
Said, '0 recored is append'

 

[ebs17]

I don't know enough about your application to be able to say anything meaningful about it. I therefore limited myself to formulating one principle.