1 Reply Latest reply on Mar 19, 2009 3:37 AM by comment_1

    Self Join not working



      Self Join not working


      I have a database containing a lot of investment transactions. When I download new data every month, I need to find out if there is any duplicate data, identify and delete them. I tried to use a self join by linking up the same table with a unique field which is quite complicated. The unique field contain an A/C No; a FundID; a Transaction Date; the Transaction Type; and the Transaction Amount. If the above 5 fields are the same, the transaction is defined as a duplicate. After I create the above join field called UniqueTranCode, I create a calculation field called Check Duplicate [Case (Count (UniqueTranCode) > 0; 1; 0)] However, the result of the calculation field shown are 1 for every record. Can anyone tell me WHAT has gone wrong ?

        • 1. Re: Self Join not working

          Well, in addition to any duplicates, a record is also related to itself. You want to be looking for Count > 1.


          Let me suggest another way to handle this. Make your "Unique" field a Text field, with auto-entered calculation (replacing existing data), using the same concatenation formula. Set the field's validation to 'Unique, Validate always'. This way, duplicates will not be imported to begin with, so you don't have to hunt for them.