how to delete records from tblA that do not belong to tblB

I have 2 tables, tblBuyer and tblItem.  Both have a field "Buyer".  I want to delete records from tblItem if tblItem.Buyer does not exist in tblBuyer.Buyer.  

Delete from tblItem - After that I am not sure how to limit records to be deleted.

Thank you very much in advance.  / hb
Who is Participating?
SharathConnect With a Mentor Data EngineerCommented:
try this.
Delete from tblItem where Buyer not in (select Buyer from tblBuyer);

Open in new window

Helen FeddemaCommented:
First make a Find Unmatched query, using the Query Wizard, to select records in tblItem that don't have a matching record in tblBuyer.  Then make it into a make-table query and run it.  Make a Delete query with the table created by the make-table query and tblItem, linking on the appropriate key field.  This will delete the records that don't have a match.

You have to make a table to do this, because if you just use the Find Unmatched query in the Delete query, you will get a message that it is not updatable.
kg6lfzAuthor Commented:
It worked wonderful.  Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.