Solved Insert INTO if not exist (1 Viewer)

[baig1984]

I want to append only data to payroll table if employee ID (Emp_ID) and attendance Date (Atn_Date) does not exist for employee in payroll table to avoid duplicate entries

INSERT INTO Tbl_Payroll ( PR_ID, Emp_ID, Full_name, Payroll_Date, Payroll_Month, Payroll_Year, Basic_Daily_Wage, Net_Daily_Wage, Payroll_Salary, Deduction_Amount, Deduction_Type, Paid_Amount, Net_Balance, Full_Pay_Days, Basic_Pay_Days, Unpaid_Days, Payment_Satus, Notes )
SELECT Tbl_Payroll_Temp.PR_ID, Tbl_Payroll_Temp.Emp_ID, Tbl_Payroll_Temp.Full_name, Tbl_Payroll_Temp.Payroll_Date, Tbl_Payroll_Temp.Payroll_Month, Tbl_Payroll_Temp.Payroll_Year, Tbl_Payroll_Temp.Basic_Daily_Wage, Tbl_Payroll_Temp.Net_Daily_Wage, Sum(Tbl_Payroll_Temp.Payroll_Salary) AS SumOfPayroll_Salary, Sum(Tbl_Payroll_Temp.Deduction_Amount) AS SumOfDeduction_Amount, Tbl_Payroll_Temp.Deduction_Type, Sum(Tbl_Payroll_Temp.Paid_Amount) AS SumOfPaid_Amount, Sum(Tbl_Payroll_Temp.Net_Balance) AS SumOfNet_Balance, Sum(Tbl_Payroll_Temp.Full_Pay_Days) AS SumOfFull_Pay_Days, Sum(Tbl_Payroll_Temp.Basic_Pay_Days) AS SumOfBasic_Pay_Days, Sum(Tbl_Payroll_Temp.Unpaid_Days) AS SumOfUnpaid_Days, Tbl_Payroll_Temp.Payment_Satus, Tbl_Payroll_Temp.Notes
FROM Tbl_Payroll_Temp
GROUP BY Tbl_Payroll_Temp.PR_ID, Tbl_Payroll_Temp.Emp_ID, Tbl_Payroll_Temp.Full_name, Tbl_Payroll_Temp.Payroll_Date, Tbl_Payroll_Temp.Payroll_Month, Tbl_Payroll_Temp.Payroll_Year, Tbl_Payroll_Temp.Basic_Daily_Wage, Tbl_Payroll_Temp.Net_Daily_Wage, Tbl_Payroll_Temp.Deduction_Type, Tbl_Payroll_Temp.Payment_Satus, Tbl_Payroll_Temp.Notes;

 

[Minty]

Put a unique index on the table combining the two fields EMP_ID & Atn_Date
This will prevent any duplicates.

 

[isladogs]

You can also create an unmatched select query using those values as filter criteria - a wizard is available for this.
Then change it to an append query so it will only append unmatched records

 

[baig1984]

Put a unique index on the table combining the two fields EMP_ID & Atn_Date
This will prevent any duplicates.

But there ll be other employees attendance for same date so if i add unique index it won't let me add their attendance for same date.

I want look for records with 2 criteria before appending emp_id and atn_date

 

[baig1984]

You can also create an unmatched select query using those values as filter criteria - a wizard is available for this.
Then change it to an append query so it will only append unmatched records

How would I append multiple values using that?

 

[arnelgp]

see post #2 for your simple solution.

 

[Minty]

But there ll be other employees attendance for same date so if i add unique index it won't let me add their attendance for same date.

I want look for records with 2 criteria before appending emp_id and atn_date

So the index I was suggesting is a combined index using both the EMP_id AND the atn_Date.
Any employee can attend on any date, once.

EMP_ID	AtnDate
1​	01/07/2021​
2​	01/07/2021​
3​	01/07/2021​
1​	02/07/2021​
2​	02/07/2021​
3​	02/07/2021​
1​	03/07/2021​
2​	03/07/2021​
3​	03/07/2021​
1​	01/07/2021​	NOT ALLOWED DUE TO DUPLICATE VALUE

 

[isladogs]

An unmatched append query will insert all records which don't already exist.
In this case, where no existing data for employees on specific dates
In other words, the same idea as a composite index.
Both methods work. Choose whichever you prefer or understand better.

 

[Minty]

There is a wizard to create an unmatched query, use that as your starting point.
Or you could spend 2 minutes adding the indexes as suggested.

 

[baig1984]

So the index I was suggesting is a combined index using both the EMP_id AND the atn_Date.
Any employee can attend on any date, once.

EMP_ID	AtnDate
1​	01/07/2021​
2​	01/07/2021​
3​	01/07/2021​
1​	02/07/2021​
2​	02/07/2021​
3​	02/07/2021​
1​	03/07/2021​
2​	03/07/2021​
3​	03/07/2021​
1​	01/07/2021​	NOT ALLOWED DUE TO DUPLICATE VALUE

seems working

 

[isladogs]

Excellent. Glad you have a solution.
Let us know if you still want to know more details about the other method