Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    21

    Unanswered: delete duplicate records in same table

    I have a table A that has following fields MRN, DATEOFLASTCONTACT, NAME. There is no primary key in this table.

    I want to delete records from the table for the same patient whose date of last contact is less than max date of last contact.
    Example:

    TABLE A: ( BEFORE RUNNING SQL QUERY )
    NAME MRN DATEOFLASTCONTACT
    JOHN 1234 12/12/2012
    JOHN 1234 12/12/2011
    JOHN 1234 11/10/2010

    I need the SQL query that can give me the following output in the same table TABLE A:

    NAME MRN DATEOFLASTCONTACT
    JOHN 1234 12/12/2012

    I also want to create a seperate table TABLE B from the intial table TABLE A as:
    NAME MRN DATEOFLASTCONTACT
    JOHN 1234 12/12/2011
    JOHN 1234 11/10/2010

    Can you let me know the SQL for above ? Thanks !!

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi there,

    SQL to find duplicate records and display the most recent date (QueryA):

    Code:
    SELECT DISTINCT tblName.Name, tblName.MRN, Max(tblName.Date) AS MaxOfDate
    FROM tblName
    GROUP BY tblName.Name, tblName.MRN;
    SQL to find duplicate records and display records for which the date IS NOT most recent (QueryB):

    Code:
    SELECT DISTINCT tblName.Name, tblName.MRN, tblName.Date, QueryA.Name
    FROM tblName LEFT JOIN Query A ON (tblName.Name = QueryA.Name) AND (tblName.MRN = QueryA.MRN) AND (tblName.Date = QueryA.MaxOfDate)
    GROUP BY tblName.Name, tblName.MRN, tblName.Date, QueryA.Name
    HAVING (((QueryA.Name) Is Null));
    Assuming that all of your fields WILL have data in them, else you will need to deal with the null values. Obviously the SQL I have posted using the SELECT statement which you will need to change should you wish to append, update or delete these records.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    Dave,

    That might work for me too! If I want to remove duplicates for a field named "myfield" can you show me what that would look like? And would I use it in an update query?

    Actually the whole story is that I made "One Big Table" in my access dB (has to be this way for export / import reasons) and I noticed that I have about 10,000 duplicate records / rows. I tried finding the reason that the duplicates are being created and removed what I though was causing it but it didn't work. I believe it's better to not cause the duplicates to begin with so I can have clean data but since I can't get er done, I just want to get rid of them. Basically I'm hoping to delete all rows that are the same in all 140 columns.

    P.S. I'm new to Access but have been learning rapidly from this forum. Thank you everyone.

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    Have a look at this link, this should explain all
    Eliminate duplicate records with this built-in Access query | TechRepublic
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  5. #5
    Join Date
    Aug 2010
    Posts
    87
    Thanks Dave

Posting Permissions

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