Deleting upmatched records

[Kenln]

I have a query displaying unmatched records.

SELECT tbl_Job_Access.Job_No
FROM tbl_Job_Access LEFT JOIN CMSqry_JCPDSC
ON tbl_Job_Access.Job_No = CMSqry_JCPDSC.JOB_NO
WHERE (
((CMSqry_JCPDSC.JOB_NO) Is Null)
);

I would like to delete the unmatched records that appear in tbl_Job_Access. Can I do it by modifying this query? It would be nice to complete this in one step.

Thank you,

 

[ecawilkinson]

Try:

DELETE tbl_Job_Access.*
FROM tbl_Job_Access LEFT JOIN CMSqry_JCPDSC
ON tbl_Job_Access.Job_No = CMSqry_JCPDSC.JOB_NO
WHERE (
((CMSqry_JCPDSC.JOB_NO) Is Null)
);

 

[CyberLynx]

And please don't forget......Backup your Database First

.

 

[Kenln]

I get the same message "Could not delete from specified tables."

 

[georgedwilkinson]

JOB_NO is null regardless of which table you test it in. If this is what you actually need then:

DELETE tbl_Job_Access.*
FROM tbl_Job_Access
Where tbl_Job_Access.JOB_NO Is Null;

That is exactly what the 2 SQL statements in this post are saying.

 

[ByteMyzer]

Try:

DELETE tbl_Job_Access.*
FROM tbl_Job_Access
WHERE tbl_Job_Access.Job_No NOT IN
 (SELECT CMSqry_JCPDSC.JOB_NO
  FROM CMSqry_JCPDSC
 )
;

 

[Kenln]

I thought this would do it, it looks good but I still get the same message.

In query designer, when I click view I see the job I want to delete, when I click run I still get "Could not delete from specified tables."

 

[Kenln]

If I change your code to an update and set a field in the appropriate record.
Then run a delete query I can get it to work.

I can use this however it would be nice to accomplish this in a single step rather than two.

UPDATE tbl_Job_Access
SET tbl_Job_Access.bDelete = True
WHERE tbl_Job_Access.Job_No NOT IN
 (SELECT CMSqry_JCPDSC.JOB_NO
  FROM CMSqry_JCPDSC
 );

DELETE tbl_Job_Access.*,
tbl_Job_Access.bDelete
FROM tbl_Job_Access
WHERE (
((tbl_Job_Access.bDelete)=True)
);

 

[georgedwilkinson]

What is CMSqry_JCPDSC? If it is not a query based on the original table, the following is exactly the same as the 2 steps listed above. If it is based on the original table, you might have problems and you need to give us more information.

DELETE tbl_Job_Access.*
FROM tbl_Job_Access
WHERE tbl_Job_Access.Job_No NOT IN
(SELECT CMSqry_JCPDSC.JOB_NO
FROM CMSqry_JCPDSC );