More than two tables in an outer join query

I've often come across situations where MS Query complains that you can't have more than two tables involved in an outer join query. I'm not sure if this occurs with every ODBC driver but it certainly does with the Microsoft Text Driver (*.txt; *.csv) driver.  I've always worked around this by using multiple queries to get the job done but I recently discovered that it seems to be a MS Query issue rather than a restriction associated with the text driver. I stumbled across this because Excel 2007 and later allow you to edit the SQL associated with your data connections without invoking MS Query.  I had a three table query with inner joins and discovered that one of the inner joins needed to be an outer join.  I made the change to the SQL manually and it worked.  MS Query still complains and will refuse to deal with the query so I often adopt a hybrid approach to dealing with such queries as follows:

• construct the query in MS Query using inner joins only;
• manually change the inner join details in the SQL generated by MS Query to outer joins

A simple example follows using three data files (Data, Data2 and Data3) each with an ID column on which they are joined. The initial SQL generated from MS Query was:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM Data.txt Data, Data2.txt Data2, Data3.txt Data3
WHERE Data.ID = Data2.ID AND Data.ID = Data3.ID

After editing the SQL became:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM (Data.txt Data
left outer join Data2.txt Data2 on Data.ID = Data2.ID)
left outer join Data3.txt Data3 on Data.ID = Data3.ID

The output of this query looks as follows:

ID	DName	D2Name	D3Name
1	Unrestricted		Unrestricted
2	Restricted	Restricted
3	Designated		Designated

The outer join operation is evidenced by the null values returned under D2Name and D3Name.


For Excel 2003 I make the changes to the SQL by opening an Immediate window in the Visual Basic Editor and typing something like [?activecell.querytable.commandtext] followed by [Enter]. This reports the first SQL above on rows two to four of the window. Then I edit the first line to read [activecell.querytable.commandtext = "SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name FROM (Data.txt Data left outer join Data2.txt Data2 on Data.ID = Data2.ID) left outer join Data3.txt Data3 on Data.ID = Data3.ID"] followed by [Enter]. This changes the SQL without the need to use MS Query.  A simple refresh of the data will show the multitable query with the outer joins in place.

I hope you will find this helpful.  It has always worked for me, but I worry that there must be some good reason that MS Query complains about such a query.

Your comments would be very welcome