I guess we're trying to figure out a pattern to the duplicate records, so that we can determine the cause.

A couple thoughts:

1. Is it the duplicates which are not showing? In this scenario, you'd be missing complete records.

2. Do you get empty values for fields for the last 2 fields? If so, that's due to your using a LEFT JOIN. In this SELECT, all nDebit1 records will be returned whether or not they have a corresponding CompanyID record. That would be an indication that the CompanyID table is not up to date. If the proper entries are made, the last 2 fields would be populated.

If you just used a JOIN, you would get only the nDebit1 records which have a corresponding CompanyID record.

Jay



>Okay. I think my problem comes with duplicate records. Every l;ast field in some os the records are duplicate. They should be there since we can have duplicate transactions.

>>>SELECT nDebit1.*, companyid.bankname AS bank, companyid.companyid AS newid ;
FROM FORCE nDebit1 LEFT JOIN companyid ;
ON ALLTRIM(nDebit1.companyid) == ALLTRIM(companyid.companyid) ;
ORDER BY nDebit1.bankname, nDebit1.settdate, nDebit1.mid, ;
nDebit1.amount, nDebit1.orgtracnum, nDebit1.dbaname, nDebit1.reasoncode, ;
nDebit1.orgtracnum, nDebit1.cleared ;
INTO TABLE ndebit2