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.

 

[Pat Hartman]

When you define the appropriate indexes, the database engine will not allow you to insert duplicate records. You need to define the business rules for what constitutes uniqueness and create the appropriate unique indexes. Autonumbers are usually used as the primary key for convenience but in reality, there may be business rules that need to be enforced. For example, in the US, an employeeID is assigned when a new employee is hired and that becomes the primary key. However, Federal tax rules say that every company has to report salary that they pay to their employees and to do that they must use the Social Security Number. The SSN is unique so no two employees would ever have the same number. Therefore, to prevent errors, you would create a unique index on the SSN field. In a different application, the rules might require a composite unique index. For example a student might fail a course and have to repeat it the next semester. The PK would still be an autonumber but in this case, the unique index would require three fields. StudentID, CourseID, SemesterID. To make a multi-field index (unique or otherwise), you must use the indexes dialog. You can't use the same technique you use to make single field indexes..

Once you get past creating the correct indexes, it sounds like you have the possibility of both new records and updates to existing records that come in in the same file and you want to insert the new records but update existing records. That is easy enough, once you define your unique index. There is a query that is sometimes called "upsert" and it does both insert and update in a single step. It works in Jet and ACE but I'm not sure it works with SQL Server. All you have to do is use a right join between the permanent table and the transaction table. You should find samples if you search.