Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: NEED HELP with finding duplicates

    Hi there,
    I'm in a fix and need some help here. I have a database in Access that has 10 or so fields. I need to find all the records in this database where two or more records have duplicate values in three fields.

    heres a sample table:

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

    So in the above example I want to evaulate the 1st, 3rd, and 5th fields to see if they match and return them. The output from the above table should be:

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

    Because the three fields have the same values in them.

    Is this possible using SQL in Access? HELP!!! Thanks for any help you can offer.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: NEED HELP with finding duplicates

    Are you saying any 3 columns, or just some particular 3 columns like col1, col3, col5 as in your example?

    If the latter, here is one way:

    select *
    from t
    where (col1, col3, col5) in
    ( select col1, col3, col5
    from t
    group by col1, col3, col5
    having count(*) > 1
    );

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    Re: NEED HELP with finding duplicates

    Originally posted by andrewst
    Are you saying any 3 columns, or just some particular 3 columns like col1, col3, col5 as in your example?

    If the latter, here is one way:

    select *
    from t
    where (col1, col3, col5) in
    ( select col1, col3, col5
    from t
    group by col1, col3, col5
    having count(*) > 1
    );
    Yah, its specifically those three fields. So anytime there is a record that has values in the three fields that match another record (the other record has the same values in the three fields) then it should return those two recrods. Basically my database should never have two or more records with matching values in these three fields so I need a way to see if there are.

    When I try your idea I get "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

    Thank you so much for your reply, I hope there is a way to do this. Thanks.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: NEED HELP with finding duplicates

    OK, let's try again (apparently IN clause doesn't work with Access?):

    select *
    from t
    where exists
    ( select count(*)
    from t t2
    where t2.col1 = t.col1
    and t2.col2 = t.col2
    and t2.col3 = t.col3
    having count(*) > 1
    );

    or

    select *
    from t
    where 1 <
    ( select count(*)
    from t t2
    where t2.col1 = t.col1
    and t2.col2 = t.col2
    and t2.col3 = t.col3
    );

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    Re: NEED HELP with finding duplicates

    Originally posted by andrewst
    OK, let's try again (apparently IN clause doesn't work with Access?):

    select *
    from t
    where exists
    ( select count(*)
    from t t2
    where t2.col1 = t.col1
    and t2.col2 = t.col2
    and t2.col3 = t.col3
    having count(*) > 1
    );

    or

    select *
    from t
    where 1 <
    ( select count(*)
    from t t2
    where t2.col1 = t.col1
    and t2.col2 = t.col2
    and t2.col3 = t.col3
    );
    I think this actually worked the way I wanted! I cant believe it. I was typing in the second one (cuz I had killed the first one thinking it was stuck in an ednless loop) and I was thinking to mysel, I dont understand how this will work, but it does. THANKS VERY MUCH, you made my day.

    I dont know if the first one works or not, it looked like Access had stopped responding so I killed it and typed in the second one. The second one looked like it had locked up Access as well so I took a lunch break. an hour later I arrive to find the exact results I was looking for sitting right here on my screen.

    Thanks again,
    James

Posting Permissions

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