Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove duplicate records (Access 2002)

    Is there a way to remove duplicates when there are two duplicate value fields. We are trying to remove students from a database that have taken the same test twice. We know how to find the duplicates but we want to be able to have a query to remove the duplicates without having to do it manually.

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

    Re: Remove duplicate records (Access 2002)

    Welcome to Woody's Lounge!

    Are the records complete duplicates, or is there a field on which you can distinguish records, an AutoNumber field for example? If the latter is true, you can create a query based on the duplicates query that selects the minimum value (for example) of the distinguishing field for each duplicate, and delete those records.

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Remove duplicate records (Access 2002)

    Hello Hans,

    Great solution. I understand it, but have no clue how to change the SQL so it will work.
    As example here the SQL of the duplicate query:

    SELECT tblTekstBestand.controle, tblTekstBestand.nummer, tblTekstBestand.[Type profiel code], tblTekstBestand.[Type profiel oms], tblTekstBestand.Peildatum, tblTekstBestand.[Type relatiegroep code], tblTekstBestand.[Code relatiegroep], tblTekstBestand.[Intern specialisme code]
    FROM tblTekstBestand
    WHERE (((tblTekstBestand.controle) In (SELECT [controle] FROM [tblTekstBestand] As Tmp GROUP BY [controle] HAVING Count(*)>1 )))
    ORDER BY tblTekstBestand.controle;

    the field [nummer] has the autonumber values.

    Could you please explane what to change to only show the lowest numbers of the duplicates?

    Thanks in advance.
    Patrick Schouten
    The Netherlands
    Greetings,

    Patrick Schouten
    (The Netherlands)

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

    Re: Remove duplicate records (Access 2002)

    The query

    SELECT Min(nummer) FROM tblTekstBestand GROUP BY controle

    will select the lowest nummer for each controle (not only the duplicates)

    The following delete query will remove duplicate records, leaving the one with the lowest nummer value. Create a backup of the table and/or the database before trying it!

    DELETE tblTekstBestand.nummer
    FROM tblTekstBestand
    WHERE tblTekstBestand.nummer Not In (SELECT Min(nummer) FROM tblTekstBestand GROUP BY controle)

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Remove duplicate records (Access 2002)

    I found this example in a book Beginning SQL that also does the trick.

    DELETE FROM FilmStars
    WHERE StarName IN (SELECT StarName FROM FilmStars as S2 WHERE S2.StarId < FilmStars.StarId);

    Just change the names to do the same thing. I dont know if this will execute quicker or not?

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

    Re: Remove duplicate records (Access 2002)

    You probably won't notice much difference with small to medium-sized tables. You'd have to test on a table with tens of thousands of records or more to find out whether one method is more efficient than the other.

    In general, though, queries using Not In (...) are relatively slow.

Posting Permissions

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