>
>
>Access Query wizard is about as useless as VFP's when it comes to complex queries. Your example does not follow my request in that there are more than 1 relationship to maintain. The from has 4 tables involved. I have been able to get an outer join to work on one parent to one child, but ODBC seems to puke on syntax when trying to get the other tables involved in the query. Take a look at my example and see the other relationships that need to be part of the query when a join is to be done.
I got it to work with three tables, but Access chokes when adding the fourth table with a "Join not supported" error. I was trying this on A97 and the Northwind DB. Obviously I don't have the same structure as you but the tables I am using have the same relationships:
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Customers.CustomerID, Customers.CompanyName, [Order Details].ProductID, [Order Details].Quantity
FROM (Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.CustomerID = 'ALFKI'
If you look at the NW DB in Access, notice the relationships between Orders, Order Details, Customers and Products.