The SELECT statement you have shouldn't choke on "duplicate" records, as any matches on the company ID should work, whether or not that is the key value.

Some thoughts:

1. If nDebit1.companyid and companyid.companyid have the same field length and have only blanks in them to pad the field (default VFP behavior), you logically should get the same results with nDebit1.companyid == companyid.companyid. You'll also take advantage of your indexes on those fields for Rushmore (with the index expression as only the field name).

However, as I mentioned earlier, filter expressions which return a variable length character can be unreliable. An ALLTRIM ("ABC ") will return a different string length than ALLTRIM ("A "). The way Fox stores the index expressions relies on a constant string length size. This may be the cause of your problem.

Eliminate the ALLTRIM from the expression from both the SELECT and from the index expression and see if that fixes the problem.

2. I notice that your last field in the SELECT is "companyid.companyid AS newid". You should already be including that value as nDebit1.companyid through the nDebit1.* command. The 'newid' raises a question -- are you doing some manipulation or change to the companyid in processing somewhere?

Jay



>I want the found as well as the not found records hence the use of the LEFT JOIN. This happens to me once a month when a real big file comes in for a bank. They have duplicate records in them a lot of the times. They really are not dupes because they are legitimate transactions. The way I found them last year was the problem records were manually put into the table. I looked at the entries and they were all the same. This results in only one of them being left in the table after the join.
>
>There are some cases where I don't have a match for the companyid. That's okay. I deal with that in another way.

>>>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