Solved Insert Query Using IN or EXISTS Clause

[Pac-Man]

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

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

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

 

[Gasman]

The error message is self explanatory?

You check one field against another.?

How can Access check against multiple fields in the subquery.?
Limit the subquery to retrieving TeacherID only.

 

[Pac-Man]

The error message is self explanatory?

You check one field against another.?

How can Access check against multiple fields in the subquery.?
Limit the subquery to retrieving TeacherID only.

If I limit it against teacherID only then it will not insert more for next months.

 

[Gasman]

You can still use the criteria, but just select the TeacherID ?

 

[isladogs]

I think you are making it more complex than necessary.
Create an unmatched query using the wizard with a left join linking the 3 fields, with the relevant TeacherIDs and the month/year filters from the form.
Then convert that into an append query so it only appends new records where those three fields are all null in the destination table.

I'm not convinced you need NOT EXISTS or a subquery at all.
I haven't got time to check the SQL further now but if what I've described isn't clear, my article on synchronising data has similar examples.
See Synchronise Data 1 - Mendip Data Systems

 

[Pac-Man]

You can still use the criteria, but just select the TeacherID ?

Thanks, it worked. I just had to uncheck the fields in the design view.

 

[Pac-Man]

I think you are making it more complex than necessary.
Create an unmatched query using the wizard with a left join linking the 3 fields, with the relevant TeacherIDs and the month/year filters from the form.
Then convert that into an append query so it only appends new records where those three fields are all null in the destination table.

I'm not convinced you need NOT EXISTS or a subquery at all.
I haven't got time to check the SQL further now but if what I've described isn't clear, my article on synchronising data has similar examples.
See Synchronise Data 1 - Mendip Data Systems

Thanks for the link. My issue has been resolved but I'll look into the link and I will be helpful for me in future (like other articles you share ).

 

[isladogs]

This is the solution I was suggesting based on an unmatched query:

INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT tblTeachers.TeacherID, CLng([Forms]![frmSalary]![txtMonth]) AS expMonth, CLng([Forms]![frmSalary]![txtYear]) AS expYear
FROM tblTeachers LEFT JOIN tblSalary ON tblTeachers.[TeacherID] = tblSalary.[TeacherID]
WHERE (((tblSalary.TeacherID) Is Null) AND ((tblSalary.PayMonth) Is Null) AND ((tblSalary.PayYear) Is Null));

I think you will find it works equally well and is more efficient (faster) as there is no subquery or EXISTS clause

 

[Pac-Man]

This is the solution I was suggesting based on an unmatched query:

INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT tblTeachers.TeacherID, CLng([Forms]![frmSalary]![txtMonth]) AS expMonth, CLng([Forms]![frmSalary]![txtYear]) AS expYear
FROM tblTeachers LEFT JOIN tblSalary ON tblTeachers.[TeacherID] = tblSalary.[TeacherID]
WHERE (((tblSalary.TeacherID) Is Null) AND ((tblSalary.PayMonth) Is Null) AND ((tblSalary.PayYear) Is Null));

I think you will find it works equally well and is more efficient (faster) as there is no subquery or EXISTS clause

Thanks a lot @isladogs. Right now I'm away from PC. I'll check and then update here.

 

[Pac-Man]

This is the solution I was suggesting based on an unmatched query:

INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT tblTeachers.TeacherID, CLng([Forms]![frmSalary]![txtMonth]) AS expMonth, CLng([Forms]![frmSalary]![txtYear]) AS expYear
FROM tblTeachers LEFT JOIN tblSalary ON tblTeachers.[TeacherID] = tblSalary.[TeacherID]
WHERE (((tblSalary.TeacherID) Is Null) AND ((tblSalary.PayMonth) Is Null) AND ((tblSalary.PayYear) Is Null));

I think you will find it works equally well and is more efficient (faster) as there is no subquery or EXISTS clause

Dear @isladogs, the query is not working, it do not insert any record for new month since TeacherID is already present in the tblSalary for previous months (is Null can't be true, I guess). I even checked by removing is Null from teacherID criteria but it didn't work either (so my guess is wrong).

 

[isladogs]

Sorry. It was air code. I should have tested it before posting

 

[Gasman]

Dear @isladogs, the query is not working, it do not insert any record for new month since TeacherID is already present in the tblSalary for previous months (is Null can't be true, I guess). I even checked by removing is Null from teacherID criteria but it didn't work either (so my guess is wrong).

So what happens if you just use

((tblSalary.PayMonth) Is Null) AND ((tblSalary.PayYear) Is Null))

 

[isladogs]

It still doesn't work. No records added
For the unmatched query to work all fields marked as Is Null need to be in both table and used as part of the left join.
That's not possible in this case
I did try changing it to:

INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT DISTINCT tblTeachers.TeacherID, CLng([Forms]![frmSalary]![txtMonth]) AS expMonth, CLng([Forms]![frmSalary]![txtYear]) AS expYear
FROM tblTeachers LEFT JOIN tblSalary ON tblTeachers.[TeacherID] = tblSalary.[TeacherID]
WHERE (((tblSalary.PayMonth)<>CLng([Forms]![frmSalary]![txtMonth])) AND ((tblSalary.PayYear)<>CLng([Forms]![frmSalary]![txtYear])));

Although that worked, it allowed the same entries to be repeated.
To fix this would probably need a subquery or other complications which mean its no simpler than what the OP already had....

 

[Pac-Man]

So what happens if you just use

((tblSalary.PayMonth) Is Null) AND ((tblSalary.PayYear) Is Null))

I did tried this but it didn't work either (as @isladogs confirmed).

To fix this would probably need a subquery or other complications which mean its no simpler than what the OP already had....

There may be other methods which might be faster and easier but I am not good at queries so until I find good alternate to this method, I am using subquery to do the job.

Thanks for efforts to help me in this regard.