Try to use UNION like:

*----------------------------
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'
UNION
SELECT Parent.Username, Parent.Phone, ' ', ' ',
StatusLookup.Status, ' '
FROM Parent, StatusLookup
WHERE Parent.ItemKey NOT IN (select Child.ItemKey from Child) AND
Parent.StatusKey = StatusLookup.StatusKey AND
Parent.Username = 'SMITHBOB'
*---------------------------------

ODBC may have problem with double ", so I would use single ' instead.
UNION need related field sizes equal. So, include as many spaces as needed in the second statement above between ' '.

Good luck


>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