Results 1 to 6 of 6
  1. #1
    James_ is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    7

    Delete records that match multiple criteria from second table.

    Hello,



    I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.

    I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.


    What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.

    The 'Surname' and 'DPS' are no problem, it's the variable position of the third field that I really need help with. I think I could do it in three seperate queries but it would definitely be better in one.


    Thanks in advance for any help,
    James.

  2. #2
    randman1 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    25
    Here's something to start with. Although this is not a Delete query, it includes a field called ToBeDeleted which is True/False. You can use this to determine if it meets your requirements. If it does, it can easily be converted to a Delete query.

    Copy & paste this in a the SQL view of a blank query:

    SELECT Sheet1.*, IIf(Not IsNull([Sheet2].[Surname]) And Not IsNull([Sheet2].[DPS]) And ([Sheet2].[Line5]=[Sheet1].[Line3] Or [Sheet2].[Line5]=[Sheet1].[Line4] Or [Sheet2].[Line5]=[Sheet1].[Line5]),"True","False") AS ToBeDeleted, Sheet2.Surname, Sheet2.DPS, Sheet2.Line5
    FROM Sheet1 LEFT JOIN Sheet2 ON (Sheet1.DPS = Sheet2.DPS) AND (Sheet1.Surname = Sheet2.Surname);

  3. #3
    James_ is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    7
    Thank you for the reply. The query seems to work perfectly at finding the right entries. How would I go about making it delete them aswell?

    Thanks, James.

  4. #4
    randman1 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    25
    DELETE Sheet1.*, IIf(Not IsNull([Sheet2].[Surname]) And Not IsNull([Sheet2].[DPS]) And ([Sheet2].[Line5]=[Sheet1].[Line3] Or [Sheet2].[Line5]=[Sheet1].[Line4] Or [Sheet2].[Line5]=[Sheet1].[Line5]),"True","False") AS ToBeDeleted
    FROM Sheet1 LEFT JOIN Sheet2 ON (Sheet1.DPS = Sheet2.DPS) AND (Sheet1.Surname = Sheet2.Surname)
    WHERE IIf(Not IsNull([Sheet2].[Surname]) And Not IsNull([Sheet2].[DPS]) And ([Sheet2].[Line5]=[Sheet1].[Line3] Or [Sheet2].[Line5]=[Sheet1].[Line4] Or [Sheet2].[Line5]=[Sheet1].[Line5]),"True","False") = "True";

    Essentially, this says: DELETE all rows WHERE ToBeDeleted = True. It will be clearer once you switch to Design view.

  5. #5
    James_ is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    7
    I got a popup saying 'Could not delete from specified tables', is this something to do with the query or is it more likely to do with my tables?

    Thanks,
    James. (apologies for being clueless )

  6. #6
    randman1 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    25
    Sorry. My work PC has Access 2003 so I could not test the query before posting. The query result is read only.

    This query deletes records with ID's 1 through 5 from Sheet1

    DELETE * FROM Sheet1
    WHERE (((EXISTS (SELECT * FROM Sheet2 WHERE [Sheet2].[Surname] = [Sheet1].[Surname] And [Sheet2].[DPS] = [Sheet1].[DPS] And ([Sheet2].[Line5]=[Sheet1].[Line3] Or [Sheet2].[Line5]=[Sheet1].[Line4] Or [Sheet2].[Line5]=[Sheet1].[Line5])))=True));

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 09-09-2011, 11:14 AM
  2. Dlookup to match two criteria and return value
    By randolphoralph in forum Programming
    Replies: 20
    Last Post: 05-20-2010, 12:27 PM
  3. Replies: 1
    Last Post: 04-29-2010, 05:22 PM
  4. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  5. Delete all records in a table?
    By bob646 in forum Access
    Replies: 1
    Last Post: 05-20-2007, 11:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums