Solved

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

Posted on 2011-03-21
3
261 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:kg6lfz
3 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
Comment Utility
try this.
Delete from tblItem where Buyer not in (select Buyer from tblBuyer);

Open in new window

0
 

Author Closing Comment

by:kg6lfz
Comment Utility
It worked wonderful.  Thank you.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now