Hello,
I'm trying to insert values into tblSalary (where these don't exist in the table already) using the following query. I used following QUERY 1 and worked but if there is even record in the subquery, then no value is inserted. I googled and read that exists clause return a Boolean value and I think if one record is found by the subquery, it returns true value and then no records are inserted by the main query. Then I tried QUERY 2 with IN clause but it is giving error message shown below:
QUERY 1
QUERY 2
Can somebody please help me achieve the purpose using a query?
Best Regards,
Abdullah
I can successfully insert the values by using following query
I'm trying to insert values into tblSalary (where these don't exist in the table already) using the following query. I used following QUERY 1 and worked but if there is even record in the subquery, then no value is inserted. I googled and read that exists clause return a Boolean value and I think if one record is found by the subquery, it returns true value and then no records are inserted by the main query. Then I tried QUERY 2 with IN clause but it is giving error message shown below:
QUERY 1
SQL:
INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT DISTINCT tblTeachers.TeacherID, CLng([Forms]![frmSalary]![txtMonth]) AS expMonth, CLng([Forms]![frmSalary]![txtYear]) AS expYear
FROM tblTeachers WHERE NOT EXISTS (SELECT tblSalary.TeacherID, tblSalary.PayMonth, tblSalary.PayYear FROM tblSalary WHERE (((tblSalary.PayMonth)=[Forms]![frmSalary]![txtMonth]) AND ((tblSalary.PayYear)=[Forms]![frmSalary]![txtYear])););
QUERY 2
SQL:
INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT DISTINCT tblTeachers.TeacherID, CLng([Forms]![frmSalary]![txtMonth]) AS expMonth, CLng([Forms]![frmSalary]![txtYear]) AS expYear
FROM tblTeachers WHERE tblTeachers.TeacherID not IN (SELECT tblSalary.TeacherID, tblSalary.PayMonth, tblSalary.PayYear FROM tblSalary WHERE (((tblSalary.PayMonth)=[Forms]![frmSalary]![txtMonth]) AND ((tblSalary.PayYear)=[Forms]![frmSalary]![txtYear])););
Can somebody please help me achieve the purpose using a query?
Best Regards,
Abdullah
I can successfully insert the values by using following query