Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query (Access2003)

    I have a query showing unmatched records in a table named Athletenames. I want to delete the records. I cannot find how to amend the query to do it.
    Any ideas, thanks

    SELECT AthleteNames.AthleteID
    FROM AthleteNames LEFT JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID
    WHERE (((JUNCTION.AthleteID) Is Null));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query (Access2003)

    Open the query in design view.
    Select Query | Delete Query.
    Make sure that the AthleteID field from AthleteNames has From and the one from Junction has Where in the Delete row.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query (Access2003)

    Thanks Hans, trust you are well. I cannot change the AthleteID from Where to From, it keeps replacing itself to Where?? Regards

    Sorry, I also did not post the delete version of the query:

    DELETE AthleteNames.AthleteID, JUNCTION.AthleteID
    FROM AthleteNames LEFT JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID
    WHERE (((JUNCTION.AthleteID) Is Null));

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query (Access2003)

    In that case, try

    DELETE AthleteNames.AthleteID
    FROM AthleteNames
    WHERE AthleteNames.AthleteID Not In (SELECT JUNCTION.AthleteID FROM JUNCTION)

    Try it on a copy of the database first!

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query (Access2003)

    Many thanks again Hans, it worked perfectly, no problem. Best regards

Posting Permissions

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