Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query to remove orphan records (Access 97)

    I have an Access 97 database which was set up without relationships between tables. Unfortunately, I now have orphan records in one table which don't have a corresponding match in a second table. I need an SQL statement that will run a Delete Query to remove these records.

    The common field between the 2 tables is 'ExtensionID'. TableA contains records with ExtensionID's that have no corresponding match in TableB.

    Help, please!!!

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query to remove orphan records (Access 97)

    Try

    DELETE tblA.*, tblA.PrimaryKey
    FROM tblA
    WHERE (((tblA.PrimaryKey) Not In (select ForeignKey FROM tblB)));

    Where PrimaryKey and ForeignKey are the fields that you would join on.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete query to remove orphan records (Access 97)

    Here's an alternative query:

    DELETE DISTINCTROW TableA.*
    FROM TableA LEFT JOIN TableB ON TableA.ExtensionID = TableB.ExtensionID
    WHERE (((TableB.ExtensionID) Is Null));
    Charlotte

Posting Permissions

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