Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Salem, Oregon, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003 Delete Query (2003 )

    I am trying what seems like a very simple delete query. I have a table that has a number of codes, one per record. I want to remove any records that have codes in an "exclude table" which has only a Key and the code. When framed as a select query, I get the subset of codes from the first table that I want to delete. When changed to a delete query, I get the error saying (paraphrased) "Access can't delete from those tables" . Looking through the threads, I see a number of problems with delete, but none that seems to fit my problem.

    Here is the query string:
    DELETE tblOtest.*
    FROM ExcludeT INNER JOIN tblOtest ON ExcludeT.[Identified Positions not needed in Overhead List] = tblOtest.pq_qual_code;

    I have attached a sample database that produces the error.

    Any insight would be a gift. Thanks in advance.

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

    Re: Access 2003 Delete Query (2003 )

    If you make the 'Identified Positions not needed in Overhead List' field the primary key in the ExcludeT table, the delete query will work as intended.

    If you don't want to change the primary key, you can use the following SQL:

    DELETE pq_qual_code
    FROM tblOtest
    WHERE pq_qual_code In (SELECT [Identified Positions not needed in Overhead List] FROM ExcludeT)

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Salem, Oregon, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 Delete Query (2003 )

    Awesome, Hans. Thanks for the clue. I was sure that the primary key was the, ahem..., key, but I I just didn't try the right combination. Another quick kill for Hans, everyone! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> You deserve a cool one.

Posting Permissions

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