Results 1 to 10 of 10
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    delete duplicate records

    Hi

    Have been sent a table which is quirky in searches. I found by running the duplicate query that I have 1400 duplicate records.

    A search on google tells me to visit each one and right click & delete.

    I looked up select distinct and tried to set it up to select all records except those that had identical fields :

    Forename
    Surname
    FullDateOfBurial

    The table has fields:

    BurialID
    ChurchID_fk
    Ficheref
    Forename
    SUrname


    Abode
    DateofBurial
    YearOfBurial
    FullDateOfBurial
    Nptes
    Age
    CeremonyPerformedBY
    PageNo
    EntryNO

    but got in a terrible mess and had to restore my backup.

    Am I approaching this the wrong way?

    I thought use select distinct to list all records in aquery -then use the make table option then delete the original and rename the new one.

    thanks

    Ian

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,373
    So with the dup records, is the entire record a dup or just those 3 fields you listed? Is BurialID unique value for each record in that table?

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Does your table have a Unique Key field? If so, I would do the following:

    1. Create an Aggregate Query, where you are grouping on the fields that you are including in your Duplicate checking, and then return the MIN value of the Unique Key Field.
    2. Then either:
    a. Create a query joining your Original Table to the Aggregate Query based on the Unique Key Field, and write the results to a new table (by changing this Query to a Make Table Query)
    b. If you instead want to just delete the duplicate records, do a Delete Query from your Main Table where the Unique Key Field is not found in your Aggregate Query.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,718
    You could:

    Backup your original (perhaps more than once)

    Create a new table with the same structure, and with this table
    Create a unique composite index on fields
    Forename
    Surname
    FullDateOfBurial

    Add a field eg RecordID autonumber.

    Then, import a copy of your original table into this new table.
    You will get an error saying XXX records were not added because of index violation or duplicate or something.
    That's fine, those are the records you don't want in the new table.

    Each record in your new table will have a numeric PK.

    Good luck

  5. #5
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    the records are identical apart from the BurialID which is unique

    thanks

    Ian

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    the records are identical apart from the BurialID which is unique
    Sounds like you then have everything in place that you need to implement one of the solutions I proposed.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    thanks a lot that worked perfectly

    cheers

    Ian

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,718

  9. #9
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    Apologies your solution worked, hadn't come accross composite indexes before! I can now see how usefull they could be

    thanks again

    Ian

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,718

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: 4
    Last Post: 12-13-2016, 09:48 AM
  3. Delete duplicate records
    By samdahlr in forum Access
    Replies: 8
    Last Post: 07-28-2014, 08:25 AM
  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