>I am trying to select data using ODBC to reach an Access Database and was wondering if someone could give me the valid ODBC SQL syntax for the following:
>
>Tables:
>
>Parent
>Child
>StatusLookup
>PriorityLookup
>
>Parent is linked to child by ItemKey
>Parent has a link to valid status in StatusLookup via a StatusKey
>Child has a link to valid Priority in PriorityLookup via PriorityKey
>
>User will filter on Parent.UserName
>
>So if I wanted to do this straight I would do
>
>SELECT Parent.Username, Parent.Phone, Child.KidFirstName, Child.KidLastName,
>StatusLookup.Status, PriorityLookup.Priority
>FROM Parent, Child, StatusLookup, PriorityLookup
>WHERE Parent.ItemKey = Child.ItemKey AND
>Parent.StatusKey = StatusLookup.StatusKey AND
>Child.PriorityKey = PriorityLookup.PriorityKey AND
>Parent.Username = "SMITHBOB"
>
>So now the ODBC problem. I need the above to be translated to an ODBC recognized statement that will give me all Parent records even when the parent has no children. So I need to do one of those great Joins, the problem is the ODBC syntax I have tried does not work. Can someone help here? Remember I still need the child and the Priority also linked through the child. TIA

If you have Access available you could use the query wizard to produce the SQL for you, and then paste that to your SPT or other code. For example, the wizard generated this query out of the Nortwhind DB which has the same relationships as your example above:

SELECT DISTINCTROW Orders.OrderID, Orders.OrderDate, 
[Order Details].Quantity, Products.ProductName, Customers.CompanyName
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID;

Although if you want all parent records you would use an OUTER JOIN between Parent and child. It's kind of convoluted, but could get you started if you are not familiar with the Access SQL syntax.
HTH