>You can look up the syntax for SELECT - SQL in VFP5 too.

Ah yes, the syntax does have the join..on, but there are no examples of its use.

Continuing this question:

Suppose table3 has 3 records for one of the reps in table1 & table2
using the clause:
From table1 t1 left join table2 t2 on t1.f1=t2.f2 left join table3 t3 t1.f1=t3.f3
where t1.f1=date() AND t2.f2=date(); ..

gives ok results when there are no matching records in table3, but if there are 3 records in table3, it'll give 3 times the counts for t1.f1, etc.

If i put a date restriction on table3 in the where clause, i exlude everything i was hoping to keep using the left joins.

Or is this going to require that i first count the matches in table3, then link to the result (which will have a unique record for each rep)

My boss is afraid to use SQL-Select because of the "smoke & mirrors" which you cannot "step through" to "see what is going on" His xbase solution is about 10 steps long and involves a lot of table traverses. I'm sure the SQL is the better method in this case. I >like< 'magic' statements that return exactly what i want - even if it takes a few tries to build the statement.