Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: Deleteing records that a joined select yealds

    I'm running Access 2003

    SELECT Samples.*
    FROM Samples LEFT JOIN Results ON Samples.SampIDBarcode = Results.SampleID
    WHERE Results.SampleID Is Null

    I want to delete the samples table records that this query yealds but when I tried...

    DELETE *
    FROM Samples LEFT JOIN Results ON Samples.SampIDBarcode = Results.SampleID
    WHERE Results.SampleID Is Null

    it said "Specify the table containing the records you want to delete.". So I tried...

    DELETE *
    FROM
    (
    SELECT Samples.*
    FROM Samples LEFT JOIN Results ON Samples.SampIDBarcode = Results.SampleID
    WHERE Results.SampleID Is Null
    )

    and it said "Could not delete from specified tables.".

    Is what I'm trying to do possible with one query or even with more than one query?

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    Another way to skin the cat is to put a Status flag on the Table and mark the Sample for deletion and then Delete those records marked for deletion. This way you do get a chance to review your records that you are removing. Perhaps not in this case, reports can be generated of deleted records before they are removed. Call me pragmatic!!!

  3. #3
    Join Date
    Jun 2006
    Posts
    72
    I've managed to get it working with the sql

    DELETE Samples.*
    FROM Samples LEFT JOIN Results ON Samples.SampIDBarcode = Results.SampleID
    WHERE Results.SampleID Is Null

    I like the idea of being able to reveiw the records before you delete them... I think I'll build that functionality into the form, thank you for the suggestion.

Posting Permissions

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