>Thank you Nick, that worked perfectly. So, what is the difference, I see the difference in the order of the JOIN and ON commands. Are there "rules" for that placement? And, why didn't the query designer do that, or did I choose things in the correct order??
>
>
>>SELECT A.account, A.namekey, A.department,;
> A.section, A.team, A.name, A.addr1_1,;
> A.addr1_2, A.city1, A.state1, A.zip1_5,;
> A.camt0, A.camt1, B.eamt0, B.eamt1,;
> B.gemp0, B.gemp1, B.temp0, B.temp1,;
> C.cproj, C.eproj, 0000000 AS cpcproj, ;
> 0000000 AS epcproj;
> FROM A ;
> LEFT OUTER JOIN B;
> ON A.account = B.account;
> LEFT OUTER JOIN C ;
> ON A.account = C.account ;
> WHERE A.department NOT IN ("16","17","18","19","20");
> ORDER BY A.account;
> INTO TABLE vic
That is the way it supposed to be, but Query Designer (and the View designer ) writes the SQL statement differently. And if you try to create the view with 2 parallel joins (like yours) in the View Designer it just will not work, complaining about "Column xxx is not found". But you can create such a view programmatically. In such cases I use the View Designer for creating quick-and-dirty SQL, then copy the SQL, rearrange joins properly and create the view programmatically. After that you must not try to open this view in the View Designer.
This usually considered as a bug in VD, but probably the VD just was never intended for too complex things.
Nick
Nick Neklioudov
Universal Thread Consultant
3 times Microsoft MVP - Visual FoxPro
"I have not failed. I've just found 10,000 ways that don't work." - Thomas Edison