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

    Unanswered: HELP!! Finding Rows with three fiels that are same

    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
    Feb 2004
    Posts
    199
    interesting task
    1) I'd use another structure for this task, I'd make 2 tables, one with record header and PK (master table) and secodn with related lines , each line representing the field. in this case it would be very easy to make a query you need.
    but anyway if you can't change the table structure, you can use another, dificult way:
    2) I'll explain you the idea and if you're good in SQL, you'll make it by yourself (if not, send me the MDB file and I'll put there query you need)
    you need to make complex join - joins assembled with OR (instead of AND) or without join and just Where condition of on two aliases of the same table. cose you need to find corresponding records for all records.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    Originally posted by Kakha
    interesting task
    1) I'd use another structure for this task, I'd make 2 tables, one with record header and PK (master table) and secodn with related lines , each line representing the field. in this case it would be very easy to make a query you need.
    but anyway if you can't change the table structure, you can use another, dificult way:
    2) I'll explain you the idea and if you're good in SQL, you'll make it by yourself (if not, send me the MDB file and I'll put there query you need)
    you need to make complex join - joins assembled with OR (instead of AND) or without join and just Where condition of on two aliases of the same table. cose you need to find corresponding records for all records.
    Hey Kakha, I posted this ins another forum and heres the SQL they posted there. It works! It takes almost an hour to run the query for some reason, but it works (I have no idea how)!

    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 just replaced the Col*'s with my field names an "t" with my table name.

Posting Permissions

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