Results 1 to 2 of 2
  1. #1
    rndmxy2k13 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    8

    Delete duplicate records and unite results

    Hi everybody,

    I know this probably has been asked a 1000 times but I don't seem to be able to edit the uncommented VBA-codes I found online to work on my database.

    I already ran a query that shows me the duplicate records of my original table.
    Now I m looking for a way to

    a) delete all duplicate records (not just specific ones by using a criteria) and
    b) unite the unique records (result of a)) with the original ones that were not displayed with the "find-duplicates-query"

    Since I'm trying to understand what I'm doing and learn something from it I'd be happy if you could give me some information about the code you guys might be posting



    Thanks in advance!

  2. #2
    rndmxy2k13 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    8
    Ok I think I finally found a solution concerning a).
    The following code seems to delete all the duplicates:

    Code:
    DELETE FROM DoubleEHQry
    WHERE ID2 NOT IN (SELECT MAX(ID2) FROM DoubleEHQry GROUP BY Feld3, Feld4, Supplier  HAVING MAX(ID2) IS NOT NULL)
    Now I'm trying to add the records which were not chosen by the "find-duplicates-query" to the results of the query posted above in order to have the complete data without duplicates.

    The code I currently have looks like this:

    Code:
    INSERT INTO DoubleEHQry ( ID, Feld1, Feld2, Feld3, Feld4, Supplier, Feld6, Feld7, Feld8, Feld9, Feld10, Feld11 )
    SELECT [EH-Control].ID, [EH-Control].Feld1, [EH-Control].Feld2, [EH-Control].Feld3, [EH-Control].Feld4, [EH-Control].Feld5, [EH-Control].Feld6, [EH-Control].Feld7, [EH-Control].Feld8, [EH-Control].Feld9, [EH-Control].Feld10, [EH-Control].Feld11
    FROM [EH-Control]
    WHERE (([DoubleEHQry].[ID] <> [EH-Control].[ID]) AND ([DoubleEHQry].[Feld1] <> [EH-Control].[Feld1]) AND ([DoubleEHQry].[Feld2] <> [EH-Control].[Feld2]) AND ([DoubleEHQry].[Feld3] <> [EH-Control].[Feld3]) AND ([DoubleEHQry].[Feld4] <> [EH-Control].[Feld4]) AND ([DoubleEHQry].[Supplier] <> [EH-Control].[Feld5]) AND ([DoubleEHQry].[Feld6] <> [EH-Control].[Feld6]) AND ([DoubleEHQry].[Feld7] <> [EH-Control].[Feld7]) AND ([DoubleEHQry].[Feld8] <> [EH-Control].[Feld8]) AND ([DoubleEHQry].[Feld9] <> [EH-Control].[Feld9]) AND ([DoubleEHQry].[Feld10] <> [EH-Control].[Feld10]) AND ([DoubleEHQry].[Feld11] <> [EH-Control].[Feld11]));
    When I try running this code Access asks me for parameters for each criteria, what am I doing wrong?
    My aim is to add the missing records without adding doubles.

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

Similar Threads

  1. Replies: 2
    Last Post: 11-08-2017, 09:04 AM
  2. Replies: 3
    Last Post: 07-14-2013, 08:24 PM
  3. Replies: 1
    Last Post: 02-19-2013, 11:28 PM
  4. Replies: 2
    Last Post: 04-13-2012, 12:53 AM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

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