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 ?