Append query not working Access 2016

Question

I am building a dentist appointment booking system for uni. I have built the whole thing and until yesterday it was working fine.

I have an append query that i would like to append the Operation_ID, Treatment_ID and Patient_ID from tblOperation_Schedule where Op_Complete = 1 and the Operation_ID doesn't already exist in the invoice table.

For some reason, the rows are not appending but i cannot spot the problem. I have been looking at it trying to think of another way it should be written but i am by no means an expert and connect see the problem. Query below.

INSERT INTO tblInvoices 
( Treatment_ID, Operation_ID, Patient_ID )

SELECT 
tblOperation_Schedule.Treatment_ID, 
tblOperation_Schedule.Operation_ID, 
tblOperation_Schedule.Patient_ID

FROM tblOperation_Schedule 
INNER JOIN tblInvoices ON 
tblOperation_Schedule.Operation_ID = tblInvoices.Operation_ID
WHERE 
(((tblOperation_Schedule.Operation_ID) Not In (Select [Operation_ID] 
from tblInvoices)) 
AND 
(((tblOperation_Schedule.Op_Complete)="1"));

Is anyone able to spot a problem with this or suggest another reason why this might not be working please? Thanks!

Answer 1

Your problem is that you make an INNER JOIN of two tables using Operation_ID, then in WHERE, you check tblOperation_Schedule.Operation_ID NOT IN ANY Operation_ID FROM tblInvoices. So your WHERE condition conflict with your JOIN condition, and it make no row in return.

At least you should change your query to this:

INSERT INTO tblInvoices 
    ( Treatment_ID, Operation_ID, Patient_ID )
SELECT Treatment_ID, 
    Operation_ID, 
    Patient_ID
FROM tblOperation_Schedule 
WHERE
    Operation_ID NOT IN (
        SELECT [Operation_ID] 
        FROM tblInvoices
    )
    AND Op_Complete = "1";