Bill,

>1. Since Guest is the PK for Guests.dbf, I assume I don't need "x1.emp" in x2?

You need x1.guest and x1.emp in X2 in order to find the min valid date for each possible guest/emp combination, ie the date of activity greater than the effective date of the commission value. x1 will have the emp value from guests.

>2. In the 3rd SQL, is there a reason you did "x1.guest = x2.guest and x1.delta = x2.delta" instead of "str(x1.guest) + str(x1.delta) = str(x2.guest) + str(x2.delta)"?
>IOW is "Condition1 AND Condition2" faster than "field1 + field2 = field1 + field2" in the Join?

If the individual columns have indexes rushmore can use them. It's also faster because no string operations need to occur. ANDing in the JOIN condition is a good thing, it can even be used to replace a where clause in some cases:

inner join Table2 on Table1.FK = Table2.Pk and Table2.SomeOtherCol = 3 && join and filter

>3. Do you think it's worth exploring whether it's worth while to index x1 and x2 so that Rushmore might speed up the 3rd SQL?
>X1 will probably average around 20,000 records each time.
>X2 and finalanswer will probably average around 3,000 records.

If it's not fast enough as is, then sure indexing on the join columns could help. How long is it taking to run? I'd do them one at a time and look at the overall time spent. I think I'd try emp first, guest second, and the dates last. But X2 is already only going to have 1 row per guest/emp combination. I'd almost bet though that without the indexes prebuilt VFP is going to build the temp indexes if it thinks it'll help.

>>Here's one way to do the whole thing with 3 SQLs, no scan loop necessary
>>
>>

* find all valid commissions per guest
>>
>>select guests.*, commissions.effdate, commissions.commission, ( guests.date - commissions.effdate ) as delta ;
>>   from commissions ;
>>   inner join guests ;
>>      on commissions.emp = guests.emp ;
>>   into cursor x1 ;
>>   having delta > 0
>>
>>* find the earliest valid
>>
>>select x1.guest, x1.emp, min(delta) as delta ;
>>   from x1 ;
>>   into cursor x2 ;
>>   group by guest, emp
>>
>>* pull the earliest rows out of x1 to get the final result
>>
>>select x1.guest, x1.date, x1.emp, x1.effdate, x1.commission ;
>>   from x1 ;
>>   inner join x2 ;
>>      on x1.guest = x2.guest and x1.emp = x2.emp and x1.delta = x2.delta ;
>>   into cursor finalanswer ;
>>   order by 1,2
>>