Solved Insert Query Using IN or EXISTS Clause (1 Viewer)

Pac-Man

Active member
Local time
Tomorrow, 04:37
Joined
Apr 14, 2020
Messages
408
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:

Screenshot 2020-12-24 160044.png


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
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:37
Joined
Sep 21, 2011
Messages
14,048
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

Active member
Local time
Tomorrow, 04:37
Joined
Apr 14, 2020
Messages
408
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

Enthusiastic Amateur
Local time
Today, 23:37
Joined
Sep 21, 2011
Messages
14,048
You can still use the criteria, but just select the TeacherID ?
 

isladogs

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
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

Active member
Local time
Tomorrow, 04:37
Joined
Apr 14, 2020
Messages
408
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

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
This is the solution I was suggesting based on an unmatched query:

Code:
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

Active member
Local time
Tomorrow, 04:37
Joined
Apr 14, 2020
Messages
408
This is the solution I was suggesting based on an unmatched query:

Code:
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

Active member
Local time
Tomorrow, 04:37
Joined
Apr 14, 2020
Messages
408
This is the solution I was suggesting based on an unmatched query:

Code:
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

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
Sorry. It was air code. I should have tested it before posting
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:37
Joined
Sep 21, 2011
Messages
14,048
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
Code:
((tblSalary.PayMonth) Is Null) AND ((tblSalary.PayYear) Is Null))
 

isladogs

MVP / VIP
Local time
Today, 23:37
Joined
Jan 14, 2017
Messages
18,186
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:

Code:
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

Active member
Local time
Tomorrow, 04:37
Joined
Apr 14, 2020
Messages
408
So what happens if you just use
Code:
((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.
 

Users who are viewing this thread

Top Bottom