Deleting primary records in one-to-many relationship

Hi all,

I have an Access DB which includes two tables to track applications to our personnel department. The first is tblApplicants, which has field ApplicantID as a key. The second table is tblApplications, which also has the ApplicantID field.

We get hundreds of applications, and we use several different queries to filter submittals out over time, and one applicant may have as many as 5 applications. So, after we delete the appropriate applications, we'd like to delete any people who don't have any applications left in the system (keeping in mind that some applicants may still have active applications, even though others were deleted).

I can't figure a way to compare the ApplicantID field in both tables, then delete all the records in the primary tblApplicants that have no occurences in the secondary tblApplications.

Make sense? Possible???

Thanks,
T

select ApplicantID
from tblApplicants
where ApplicantID
not in
(select ApplicantID from tblApplications)

this will give you all applicantID. Or you could generate a script as

select 'delete tblApplicants where ApplicantID = ' + cast(ApplicantID as varchar(20)
from tblApplicants
where ApplicantID
not in
(select ApplicantID from tblApplications)

Thanks! That did the trick. It seems so obvious now, but that's why I'm in the newbie chamber...
T