Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2005
    Posts
    11

    Unanswered: Get a list of duplicates

    What is the best way to get a list of duplicates for this situation.


    TableA

    fields a, b, c, d, e

    I want a list of all the records where c and d are equal.

    a, b, c, d, e
    1, 1, 1, 1, 1
    2, 2, 2, 2, 2
    3, 3, 1, 1, 3
    4, 4, 2, 2, 4
    5, 5, 1, 2, 5


    I want the result to bring back
    1, 1, 1, 1, 1
    3, 3, 1, 1, 3
    2, 2, 2, 2, 2
    4, 4, 2, 2, 4

    Do I need to create another table or can a simple query get me the answer?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    126
    I agree with M Owen
    Last edited by grrr223; 02-04-05 at 13:47. Reason: I read your post for real this time

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Why not just : SELECT * FROM TableA WHERE (C=D);
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jan 2005
    Posts
    11
    Thanks for the reply, but I didn't do a very good job of expressing what I was looking for.

    C does not have to equal D.

    1, 2, 3, 4, 5
    6, 7, 3, 4, 5

    Would be another example of a duplicate.

    Thanks

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    Okay, after saving the world from my inability to read what the real question is, I am confused as to what you are really trying to ask.

    Do you just want to sort the records by C and D?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by smash
    Thanks for the reply, but I didn't do a very good job of expressing what I was looking for.

    C does not have to equal D.

    1, 2, 3, 4, 5
    6, 7, 3, 4, 5

    Would be another example of a duplicate.

    Thanks
    Confusion reigns on Friday ... Try running the Find Duplicates Query Wizard
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you mean something like:
    Code:
    SELECT *
       FROM TableA AS a
       WHERE 1 < (SELECT Count(*)
          FROM TableA AS z
          WHERE  z.c = a.c
             AND z.d = a.d)
    -PatP

  8. #8
    Join Date
    Jan 2005
    Posts
    11
    Its okay, I am having a hard time trying to express what I am after.

    I have one table, I'll call table.

    I have 5 fields per record. (a, b, c, d, e)
    If two records have identical information contained in fields c and d (both fields must be the same on the two separate records), they are duplicates.

    Using my example from before....

    9, 9, 6, 6, 9
    and
    9, 9, 6, 3, 9
    are not duplicates

    2, 1, 3, 4, 9
    and
    7, 7, 3, 4, 7
    are duplicates

    another set of duplicates would be
    7, 7, 4, 4, 7
    6, 6, 4, 4, 7

    Hopefully I have explained this a little more clearly. I was having a hard time trying to express what I was after.

    Thanks again

  9. #9
    Join Date
    Feb 2004
    Posts
    126
    Smash, I think Pat's answer will give you what you need.

    That's pretty cool Pat. It always amazes me how much power subqueries add to allow relatively simple SQL statements to do so much.

    Too bad subqueries always make the graphic query designer to crash in Access .

  10. #10
    Join Date
    Jan 2005
    Posts
    11
    Quote Originally Posted by M Owen
    Confusion reigns on Friday ... Try running the Find Duplicates Query Wizard
    Is that on office xp? I tried following an example in an office 2003 tutorial, but some of those options werent available to me. Do I need to turn it on somehow?

    Sorry for the extreme noobie question, but I'm trying to learn, I SWEAR

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by grrr223
    Too bad subqueries always make the graphic query designer to crash in Access .
    I don't think I've ever had a sub-query crash the graphic query designer. How do you make it crash?

    -PatP

  12. #12
    Join Date
    Jan 2005
    Posts
    11
    Looks like mine crashed too.

    I ran the query and everything goes grey and it stays on the hour glass.

    It's been like that for a few minutes and a check of my task manager says its not responding and at 95% cpu.

    Any suggestions?

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by smash
    Is that on office xp? I tried following an example in an office 2003 tutorial, but some of those options werent available to me. Do I need to turn it on somehow?

    Sorry for the extreme noobie question, but I'm trying to learn, I SWEAR
    Did you try: Queries->New->Find Duplicates Query Wizard ....
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Feb 2004
    Posts
    126
    I'm using an ADP so maybe that has something to do with it.

    But if I try to save a query that has a subquery in it it crashes Access without fail. So I have to be careful to edit and save those only in the SQL view.

    I think this is actually the only time I have made Access crash, and I've learned how to avoid it, but without fail, if it has a subquery it crashes.

  15. #15
    Join Date
    Jan 2005
    Posts
    11
    Quote Originally Posted by M Owen
    Did you try: Queries->New->Find Duplicates Query Wizard ....
    WOW I defintley road the short bus to work today

    Thanks for the help I found it and it helped!

Posting Permissions

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