Results 1 to 8 of 8
  1. #1
    tazui1982 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    19

    How can I delete 142 duplicates using VBA code or Delete Query

    Hi all,
    So I worked on my current database a little bit and found that I had 152 duplicates, I then manually go to "tblEmployeeSkills" table and delete data, which was tedious and took roughly 12-15 secs per record.


    Can you please tell me how to automatically delete them without having to do manual deleting. Thanks

    Click image for larger version. 

Name:	duplicates.jpg 
Views:	22 
Size:	148.8 KB 
ID:	30333

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to be clear about what is a duplicate - If processID and MIS fields are creating the duplicate, explain the business rule that allows them through in the first place and then explain the basis for which one should be allowed - so the other can be deleted

  4. #4
    tazui1982 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    19
    Thanks for your reply, however that didn't help because I don't have Primary key duplicate problem, it's trickier, because Primary keys are not duplicated but other fields are. Please take a look at the pic below:
    Click image for larger version. 

Name:	duplicates_employees.JPG 
Views:	20 
Size:	147.3 KB 
ID:	30336

  5. #5
    tazui1982 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    19
    Quote Originally Posted by Ajax View Post
    you need to be clear about what is a duplicate - If processID and MIS fields are creating the duplicate, explain the business rule that allows them through in the first place and then explain the basis for which one should be allowed - so the other can be deleted
    Hi,
    The business rule says that there should be no replicates. For example on 5/27/2017 EmpID 1184 (Employee name: John, for instance) has ProductID 1 (Production model he's producing) and processID 52 (Skill that he has to produce that model), then the next days it should be the same. The problem here is that sometimes, more than 1 trainer enters training data for the same employee on different days, thus creating duplicates. I'm still working on preventing or giving trainers notice if duplicates are being created. For now, it's still happening and I'm looking for ways to delete it everyday.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    One way to prevent is to set multi-field index in table. This will prevent duplicate combinations. However, that will trigger Access message that is not too friendly. Alternatively, use code in form BeforeUpdate event to look for existing record before committing the new one. Or if there is a lot of data entry and you want to prevent wasting user time, do the record search after the critical values are input and immediately commit the record to table if no existing record found then user continues with rest of input.

    Date is a reserved word and should not use reserved words as names for anything. Better would be EnterDate.

    Also advise no spaces in naming convention. Why does query show 'Field' in field names? If you must include a qualifier, follow the same convention you have for tables and forms, fldProcessID or fld_ProcessID.
    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.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The records you are showing are not duplicates if at least 1 field value is different.
    My first thought is that there is something in your process logic that allows "duplicates".

    If you are using a surrogate key as Primary, then you should create a unique composite index of the "real" fields to avoid "duplicates".

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The business rule says that there should be no replicates. For example on 5/27/2017 EmpID 1184 (Employee name: John, for instance) has ProductID 1 (Production model he's producing) and processID 52 (Skill that he has to produce that model), then the next days it should be the same
    I don't see any of that in the data you have provided

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

Similar Threads

  1. Delete Duplicates
    By RGatDP in forum Access
    Replies: 1
    Last Post: 05-25-2016, 09:08 AM
  2. Replies: 3
    Last Post: 01-18-2016, 01:55 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. Replies: 2
    Last Post: 01-24-2012, 02:16 PM

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