Results 1 to 6 of 6
  1. #1
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82

    how do I change this find-duplicated query to ignore duplicates with the same value elsewhere

    I have a find-duplicates query created by the query wizard and then changed some by me. Right now it gives me all dups (except those with "UNIQUE" in a text field). But what I need is for it to filter out dups that have the same value in one field, and I can't figure out how to do that.



    This is the find-dups query (which, BTW, is querying a query that combines two tables)

    Code:
    SELECT FDRS.Title, FDRS.RecordingID, FDRS.SongID, FDRS.AltTitle
    FROM FDRS
    WHERE (((FDRS.AltTitle)<>"UNIQUE")) OR (((FDRS.SongID) In (SELECT [SongID] FROM FDRS As Tmp GROUP BY  [SongID] HAVING Count(*)=1 )) AND ((FDRS.AltTitle Or FDRS.AltTitle) Is Null))
    ORDER BY FDRS.Title;
    This will give me results like:

    Code:
    Title      RecordingID   SongID    AltTitle
    What       7               5         MASTER
    Who       12               9             
    Where     15               9             
    Why       22              11         DELETE
    Note that the middle two rows have the same SongID. I don't want to see those. I only want to see rows where there are duplicate titles where those titles have different SongIDs.

    Can this be done?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You have to clearly define what a duplicate is. And, if you want to further distinguish records based on some other field(s), you need to identify specifically --the computer will do what you tell it, so you better be correct - what is in and what is out.

    Do you have some sample data showing some initial set; then what you want as the result set?

  3. #3
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    Okay, an explanation of how this would work below.

    To try and clarify, here's what I would try if I don't get an answer here on how to do this. I would try to create a query that only shows rows where SongID is unique, and then use my existing duplicate query on *that.* That's basically what I'm looking to do in the query; strip out every row that doesn't have a unique SongID before checking for duplicates.

    Here's my sample data set and results:

    Okay, let's say we start with a table with this data :
    Code:
    Title        RecordingID      SongID    AltTitle
    What           1                     1
    What           2                     3         MASTER
    What           3                     3
    Who            4                     7
    Who            5                      8        DELETE
    Where         6                      11
    Where         7                     12
    Why             8                    14
    Why             9                    14
    Why           10                     16      UNIQUE
    Which        11                    18

    I would want the query to give me:
    Code:
    Title        RecordingID      SongID    AltTitle
    Who            4                     7
    Who            5                      8        DELETE
    Where         6                      11
    Where         7                     12
    Here's an explanation for why that would be the result if I'm looking for title duplicates that don't have SongID duplicates and don't have an AltTitle of "UNIQUE":

    Code:
    What           1                     1
    What           2                     3         MASTER
    What           3                     3
    The second and third "What" have the same ID, therefore they shouldn't be in the results. If we've removed those two then the first row is no longer a double of anything so it wouldn't be in there either (although if that would make the query too complex I would be fine with the first row remaining; I would just then mark it as UNIQUE to stop it from displaying).

    Code:
    Who            4                      7
    Who            5                      8        DELETE
    The two "Who" rows have different SongIDs so they would be part of the results.

    Code:
    Where         6                      11
    Where         7                     12
    Same with the "Where" rows.

    Code:
    Why         8                         14
    Why             9                    14
    Why           10                     16      UNIQUE
    As with the "What" rows, two of these have matching SongIDs so wouldn't be part of the results, and that would make the third one also not a duplicate.


    Code:
    Which        11                    18
    Title is not a duplicate, so not part of the results.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I used your sample data and called my Table "Recordings"

    Try these 2 queries
    QTitlesWithRepSongId
    Code:
    SELECT Count(Recordings.SongID) AS CountOfSongID, Recordings.Title
    FROM Recordings
    GROUP BY Recordings.Title
    HAVING (((Count(Recordings.SongID))>=2));
    Gives:
    CountOfSongID Title
    3 What
    2 Where
    2 Who
    3 Why


    QUniqueSongsDupTitles
    Code:
    SELECT Recordings.Title, Recordings.SongID
    FROM 
    QTitlesWithRepSongid INNER JOIN Recordings 
    ON QTitlesWithRepSongid.Title = Recordings.Title
    WHERE QTitlesWithRepSongid.CountOfSongID Mod 2 =0
    ORDER BY SongID;
    Gives:

    Title SongID
    Who 7
    Who 8
    Where 11
    Where 12

  5. #5
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    Can't find a way to delete this followup, but nevermind. I just created a *third* query on top of my second query that strips out everything with a doubled SongID. Maybe a bit cludgy, but this is for a short-term project so cludgy is fine.

    The first query gives the results as you indicated, but the second one doesn't. It gives me all duplicate titles, even those that share the same song id. I don't know why you're getting different results.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please see the attached. Tell me what isn't working. I used your data.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 3
    Last Post: 02-08-2019, 06:32 PM
  2. How to ignore duplicates in a query
    By badmem in forum Queries
    Replies: 3
    Last Post: 07-19-2016, 04:15 PM
  3. Replies: 2
    Last Post: 05-24-2016, 08:01 AM
  4. Ignore duplicates in one field only
    By inhops in forum Queries
    Replies: 2
    Last Post: 06-10-2015, 02:30 AM
  5. Replies: 2
    Last Post: 06-13-2013, 09:10 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