Results 1 to 2 of 2
  1. #1
    Join Date
    May 2007

    Unanswered: Delete query with inner join doesn't work


    Please have a look at the attached database. Very simple database: 2 tables. One table (bigtable) contains records, from which a certain number of records need to be deleted. The other table (remove) contains records that need to be removed from bigtable.

    I created a query (matchingRecords) that shows which records match between the 2 tables. There are 124 records. These need to be deleted from bigtable.

    I created a query (delete_does_not_work), which - you guessed it - doesn't work.

    Why does this query not work? I created it with the visual designer of access. If you "preview" the query, it shows the correct 124 records that need to be deleted, but the actual delete doesn't work.

    Now, from the research I have done, the query appears not to be "updateable".

    How can I write a query that would do this delete. My apologies for this noob questions.

    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    I don't know 'exactly' why, but I do know that when you are joining two tables adn you want the delete to work, Access needs to be sure that the record you want to delete is uniquely identified.

    Even though you may have unique SceneID in the Remove table, Access doesn't know that for sure. So by adding a Primary key to the SceneID of the Remove table it makes it clear which record to remove.

    Another suggestion that I was going to make before I saw your database was to setup Relationships (which you already did) this also helps Access understand how data relates.

Posting Permissions

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