>>Can someone explain why I get an error from:
>>SELECT Travauth.travid, Travauth.actid, Travauth.empid, ;
>> Travauth.tanum, Travauth.dcn, Travauth.amount, ;
>> Travauth.bgndate, Travauth.enddate, Travauth.destid, ;
>> Accounts.account, Accounts.allotment, Destination.city, ;
>> Destination.state ;
>> FROM travel!TravAuth ;
>> LEFT OUTER JOIN travel!Destination ;
>> LEFT OUTER JOIN travel!Accounts ;
>> ON Travauth.actid = Accounts.actid;
>> ON Travauth.destid = Destination.destkey;
>> ORDER BY Travauth.empid, Travauth.tanum
>>
>>The error is: SQL: Column 'ACTID' is not found
>>
>>If I switch the order of the JOIN and ON statements, I get the error:
>>
>>SQL: Column 'DESTID' is not found
>>
>>If I have the source tables open before I issue the command, it works. All fields exist, and I can create the view in View Designer. Once I save and exit the designer, I can not edit it in View Designer because I get the error.
>>
>>Once the view is created, the source tables have to be open before I open the view.
>>
>>TIA.
>
>Mark, don't hate JOINs, hate the View Designer. :) It does not allow to create 2 parallel joins from one table to 2 another. Create your view programmatically an it will work. Just do not open it in the VD after that.
>
>Nick

Sorry. To be more exact, I am trying to do this with code. I left off the Create SQL View Trav_V1 as at the beginning of the snippet. The problem still exists. I have added the TravAuth.ActId and Accounts.ActId fields to the select list and still get the error. If the source tables are opened first, then it works both when created and when the view is opened. The view can not be opened unless the source tables are open first. This makes no sense!

If I do this with standard SQL instead of VFP SQL, it works. But to get all the records (including those with null values in the fields populated by KeyIds from lookup tables), I have to include 1 unions per lookup table (the Accounts and Destination tables are lookup tables).

Anyone tell me how to get all travel records and matching Destination descriptions and Account descriptions using VFP joins?