Results 1 to 3 of 3

Thread: Deleting primary records in one-to-many relationship

  1. #1
    Join Date
    Mar 2003
    Posts
    2

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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)

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Smile

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •