Problem deleting records

[Jim Jones]

I have 2 tables. The first field of each table is the same - I call it
Candidate_ID - other fields are different.

In Table1, there is only one instance of each value of Candidate_ID, whereas
in Table2 each value of Candidate_ID may occur more than once. There are
many values of Cadidate_ID in Table2 which do not occur in Table1.

For each unique value of Candidate_ID in Table1, I need to delete all
records which contain the same value of Candidate_ID in Table2.

I thought it would be easy, and since my VBA is still not so good, I have
been trying to use an SQL statement derived from the Query Design View - so
far without success. Perhaps it can't be done with SQL.

Any help would be much appreciated

Jim Jones
Botswana

 

[Allen Browne]

I think this is what you want:

DELETE FROM Table2
WHERE EXISTS
(SELECT Candidate_ID
FROM Table1
WHERE Table1.Candidate_ID = Table2.Candidate_ID);

If subqueries are a new idea, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Jim Jones]

Allen

Many thanks

I should have been able to do this one myself!

Jim Jones
Botswana