>>>>
>>>>Brett,
>>>>Would you try this :
>>>>
>>>>SELECT P.Username, P.Phone, C.KidFirstName, C.KidLastName,
>>>>S.Status, Pr.Priority
>>>>FROM force Parent P ;
>>>>left join StatusLookup S on P.StatusKey = S.StatusKey ;
>>>>left join Child C on P.ItemKey = C.ItemKey ;
>>>>left join PriorityLookup Pr on C.PriorityKey = Pr.PriorityKey ;
>>>>WHERE P.Username = "SMITHBOB"
>>>>
>>>>Cetin
>>>
>>>What does the FORCE do? And why did you convert Parent to P, Child to C etc?
>>BTW I'm not sure if access driver supports 'force'
>>Cetin
>
>
>Tried your solution and I get a:
>ODBC Error Code = 37000 (Syntax error or access violation)
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
>
> error that ODBC had problems with the FROM statement. I removed the FORCE and still no good. The ODBC query builder cant seem to handle what I need either. I am at a lost on how to get parent records when there is no children and I have lookup tables to get data from. The ODBC Query builder can handle Parnet to child as a Right Outer Join, but when I add one more table it generates an error when executing.
Brett,
This time I tested on a sample mdb :)
lcSQL="SELECT p.username, c.childname,"+;
"S.Status, Pr.Priority"+;
" FROM ((parent P INNER JOIN StatusLookup S ON p.statuskey=S.Statuskey) "+;
" LEFT JOIN child C ON p.itemkey=c.ItemKey) "+;
" LEFT JOIN PriorityLookup Pr ON c.PriorityKey=Pr.PriorityKey"+;
" where P.UserName = 'SMITH'"
It was parentheses to support 'force' in VFP I think !!!
Cetin