Very peculiar. I tried to simplify the example for ease of explaination. Instead of field3 > 15, lets say that if field3 = "Collateral" then I want table2.field4, which has the description of the collateral. Also, table2 has duplicates on cust_no.


Three test cases.
Table1 has 65 rows. cust_no is unique. table2 has 303,000 rows and cust_no is not unique. I am table1 INNER JOIN table2 on cust_no.

Three test cases and I do not understand the results.
1) No columns returned from table2. 65 rows returned.
2) Return field4 (without the if field3 = "collateral"),203 rows returned.
3) IIF (as your example) to test field3 then return field4 or "", 95 rows returned.
4) WHERE field3 = "Collateral", then 33 rows are returned.


I really cannot understand the difference, especially between 2 & 3.

>SELECT cust_no, field1, field2, IIF(table2.Field3>5,Table2.Field3, SPACE(len_of_field3)) AS Field3;
> FROM table1;
> LEFT JOIN Table2 ON Table1.Cust_no = Table2.Cust_No;
> INTO CURSOR cCursor
>Is that what You need?


>>Have table1 with cust_no, field1, field2. table2 has cust_no, field3. want to create a table with table1 fields and table2.field3 if field3 > 5. Want to end up with the same number of record from table1.
>>
>>The WHERE field3 > 5 makes me lose records from table1.
>>
>>thanks
>>
>>Brenda