Results 1 to 5 of 5
  1. #1
    access360 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Posts
    2

    Delete Specific Duplicates

    I have Access 2013, and am confused on how, if at all possible, to delete specific duplicate values. I have three columns of data,Enrollment_Key, StudentID and Enrollment Date, and I want to delete all the duplicate studentID's on the same date. So if a student had more than one enrollment on the same day, i want the student ID to show up only once, but if the student had more than one enrollment, but they were on different days, it's fine. I basically want to make sure there aren't any duplicate student id's on the same day. Does anyone know what SQL code i can use?
    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Something must make the records unique, an autonumber field can do that. Then delete records that are less then or greater than the max or min ID for each student for each date. Review http://www.codeproject.com/Articles/...-in-SQL-Server
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to june7's comment, what exactly makes a record in your table unique.
    An autonumber (surrogate key) could do that; also a composite primary key composed of
    Enrollment_Key and
    StudentID and
    Enrollment Date could also work.

    If you use an autonumber, then you should make a unique composite index on
    Enrollment_Key, StudentID and Enrollment Date. This will prevent duplicates.

    So you could build a new table with one of these options, then import the data from your existing table to this new table. Duplicates will not be appended to the table; they will be rejected on key violations. This will result in the table not having duplicates and will prevent duplicates in future. An attempt to add a duplicate will give a 3022 error (I believe that's the number) which you can check for and put out your own "duplicate warning" message.

  4. #4
    access360 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Posts
    2
    Okay i tried this, but it wouldn't let me use that index as there were already duplicates. how exactly would i set this index on a new table then append the data to the new table? Please note im dealing with over 2 million rows of data.
    Thank you

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you not understand about the instructions in the referenced links? One shows how to set compound index and one shows how to remove duplicate records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. I've found duplicates but how do I delete?
    By rocktap in forum Queries
    Replies: 6
    Last Post: 04-20-2015, 07:08 PM
  2. Delete duplicates in a Querry
    By sdc1234 in forum Queries
    Replies: 1
    Last Post: 02-19-2014, 06:35 PM
  3. Delete all duplicates except one
    By Tom123 in forum Queries
    Replies: 5
    Last Post: 02-11-2014, 07:36 AM
  4. Replies: 8
    Last Post: 02-09-2014, 07:25 PM
  5. auto delete duplicates
    By sammiantha in forum Access
    Replies: 2
    Last Post: 04-17-2012, 02:37 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