Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    18

    Unanswered: Help with filtering duplicates

    Hmmmm... I hope I can describe my problem accurately.

    I have a SQL-table table which is the only source for all my tables, queries, forms, reports and it's relationship to the rest of the database needs to remain read-only. From this source table, I have three fields/columns: A (case numbers), B (Product model numbers), and C (product serial numbers). Here is an example of my sample SQL table:

    Case1, Widget1, SN123
    Case1, Widget1, SN123
    Case2, Widget2, SN234
    Case3, Widget3, SN345
    Case4, Widget1, SN123

    I am trying to set up a query, or more likely a set of queries, that will identify all sets of duplicate SN and model numbers, though only if they have unique case numbers.

    I don't want my final query to show:
    Case1, Widget1, SN123
    Case1, Widget1, SN123
    Case4, Widget1, SN123

    I want my query to show:
    Case1, Widget1, SN123
    Case4, Widget1, SN123

    and not show the duplicated
    Case1, Widget1, SN123

    An action query that would merely delete all the duplicate rows out of the SQL table is not possible; I am only allowed to filter out all duplicate records using a query. Soooo... I need to be able somehow identify one instance of duplicate entries as a UNIQUE, then all the remaining, duplicate-associated records as DUPLICATE.

    Case1, Widget1, SN123, Unique
    Case1, Widget1, SN123, Duplicate

    Provided I can find a way to do that, I can then set up a follow-up "Duplicate" query to show all of the duplicate model numbers and serial numbers that have unique case numbers.

    I hope I explained what I'm looking for clearly enough.

    Any assistance would be GREATLY appreciated.

    Thanks!

    Malvaro

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    ..

    Make a count
    Pull down the three fields that you want and then run a count. As long as those three fields are unique it won't filter, but if all three are equal, it will count and by design filtering out your dups.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by mr. blonde
    Make a count
    Pull down the three fields that you want and then run a count. As long as those three fields are unique it won't filter, but if all three are equal, it will count and by design filtering out your dups.
    I kicked that the Count idea around yesterday, but I didn't have any luck.

    The problem with that comes into play when the Count function counts all the duplicate case number sets together (which Access is supposed to do). That being, I can't identify one record from each individial duplicate group as unique, and everything else in that same group as duplicate.

    Case1, Duplicate
    Case1, Duplicate
    Case1, Duplicate

    I need one unique from every duplicate set to compare against all the original completely unique records from the original table. That way I can identify every model number & serial number duplicate match each associated with 2+ unique case numbers.

    Case1, Unique
    Case1, Duplicate
    Case1, Duplicate

    I do have a sequential id number field available for every record (unique or duplicate), but I'm not sure if I can flag one record from every duplicate set as unique and the rest as duplicates.

    Does that make more sense?

  4. #4
    Join Date
    Feb 2004
    Posts
    214

    ...

    You can try this.
    Make a blank table and dump all records that have a count of 1 only (non dups)

    Then make a query with a count of 2 or greater (dups).
    Link in the above dups query back to main table and bring your pk.
    Then do a min value on the pk and dump that record into the above table.

    Will that work?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  5. #5
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by mr. blonde
    You can try this.
    Make a blank table and dump all records that have a count of 1 only (non dups)

    Then make a query with a count of 2 or greater (dups).
    Link in the above dups query back to main table and bring your pk.
    Then do a min value on the pk and dump that record into the above table.

    Will that work?
    Hmmmmm, I see how that might work though. Though the final solution isn't for a one-time calculation rather for an end-user driven reporting database. So would a macro have to be written to clear the "dumping-ground" table every time, then re-load it using the two query (2< (uniques) and >1 (dups)) results?

    How much does this being an end-user reporting database complicate things?

  6. #6
    Join Date
    Feb 2004
    Posts
    214

    ...

    Quote Originally Posted by Malvaro
    How much does this being an end-user reporting database complicate things?
    Very little. Just make sure to set the warnings to no so that they don't get a bunch of pops ups asking if they are sure they want to delete, append, yada yada yada.

    And yes, you should create a series of events that would delete the data, append the new data, then open the report.

    You can do this in a macro or vb code it in the command button running the report. I would go with the later but both will accomplish the exact same thing.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •