>SELECT Rteledgr.tsurname, Rteledgr.tinitials, Rteledgr.ttitle,;
> Rteledgr.transcode, Rteledgr.paymethod, Paymeths.pmshorttxt,;
> Trancode.tcshorttxt;
> FROM rating!rteledgr INNER JOIN rating!paymeths;
> INNER JOIN rating!trancode ;
> ON Rteledgr.transcode = Trancode.transcode ;
> ON Rteledgr.paymethod = Paymeths.paymethod
>

When you use nested joins like this (where you list a series of tables, then all the ON clauses), VFP matches up the ON clauses in the reverse order of the tables. That is, the first ON clause applies to the last two tables listed. Then the next ON clause applies to the result of the first join performed and the the third table from the end.

In your query, what's happening is that PayMeths and TranCode are being joined using the condition Rteledgr.transcode = Trancode.transcode. Depending on the situation, one of two things happens. Either the Rteledgr table hasn't yet been opened, in which case you get an error, or it is open, in which case the current record in that table is used and matched against each record in TranCode. Clearly, either case is wrong.

To make this query work using this syntax, you need to change the order of the tables, like this:

SELECT Rteledgr.tsurname, Rteledgr.tinitials, Rteledgr.ttitle,;
Rteledgr.transcode, Rteledgr.paymethod, Paymeths.pmshorttxt,;
Trancode.tcshorttxt;
FROM rating!paymeths;
INNER JOIN rating!trancode ;
INNER JOIN rating!rteledgr ;
ON Rteledgr.transcode = Trancode.transcode ;
ON Rteledgr.paymethod = Paymeths.paymethod


With three tables, it is possible to get the Query Designer to generate the correct query. You just have to add the tables in the right order. However, as soon as you're dealing with more than three tables, the QD can't do it, so you might as well get used to writing your queries by hand.

As you've discovered, an alternative is to use what I call the sequential syntax, where you list each pair of tables, immediately followed by the ON clause for them. In this case, you'd use:

SELECT Rteledgr.tsurname, Rteledgr.tinitials, Rteledgr.ttitle,;
Rteledgr.transcode, Rteledgr.paymethod, Paymeths.pmshorttxt,;
Trancode.tcshorttxt;
FROM rating!rteledgr ;
INNER JOIN rating!paymeths;
ON Rteledgr.paymethod = Paymeths.paymethod
INNER JOIN rating!trancode ;
ON Rteledgr.transcode = Trancode.transcode ;

Hope this clears things up.

As for references about this topic, I've written about it a number of times. I covered it at several DevCons and other conferences and I'm pretty sure I've written about it in FoxPro Advisor in the Ask Advisor column at some point.

Tamar