Results 1 to 3 of 3

Finding Duplicates with slight name-change

  1. #1
    Shmueldabomb441 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    2

    Finding Duplicates with slight name-change

    I have an access database with over 40,000 .mp3 audio lectures paired with a SQL server. Each lectures is stored in a table in the .accdb with three columns, the lecture title, the unique LectureID, and the category of the lecture. I have found many duplicate lectures, but one says "xyzabc" and one says "xyzabc.MP3". I am not sure if a macro is actually the right tool for this, but how do i find(and delete) all of the duplicates? I am very unlearned in these topics so please try and "simplify" it, but if neccesary, i can consult with more learned people.



    Thanks so much!

  2. #2
    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
    14,327
    As I understand your post - you have a bunch of files. Each file has a filename and an extension. If you look at the file name only, not the complete path and not the extension, I think you could find duplicate /replicates with the same file name.

    If you store the name and extension in a single field, you'll have to restrict your match criteria to the file name only.

    Good luck.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,349
    A macro is not correct tool. But VBA along with query should be. Still won't be simple. Following is example query to determine which names are duplicated:

    SELECT IIf(InStr([title],"."),Left([title],InStr([test],".")-1),[title]) AS T, Count(ID) AS C
    FROM tablename
    GROUP BY IIf(InStr([title],"."),Left([title],InStr([test],".")-1),[title]);

    Automating deletion would be tricky part - review http://www.accessrepairnrecovery.com...crosoft-access. However, that method will not account for different categories (if that has happened for same file) because aside from variation in filename, different categories actually make records unique. You will have to make case-by-case decision on which category should be retained.

    Should a lecture be allowed multiple categories? If yes, then need a related dependent table (or {yuck} use a multi-value field). Are there already related tables dependent on this one?

    Standardize data - either all should have extension or none.
    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. Finding Duplicates across 2 fields
    By DaveT99 in forum Queries
    Replies: 5
    Last Post: 03-08-2018, 12:04 PM
  2. Finding Duplicates
    By yharris217 in forum Queries
    Replies: 5
    Last Post: 10-24-2017, 10:35 AM
  3. Finding Duplicates within a group
    By murfeezlaw in forum Queries
    Replies: 3
    Last Post: 10-01-2014, 07:38 PM
  4. Finding duplicates in two fields
    By skipnick in forum Access
    Replies: 6
    Last Post: 12-10-2013, 01:29 PM
  5. Replies: 1
    Last Post: 05-02-2012, 01:33 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
  •  
Tech Forums: Microsoft Office Forums