Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Thursday 29 November 2012

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

9 comments:

  1. I have been looking for the past few months on how to get this to work, thank you for ending my turmoil!

    ReplyDelete
    Replies
    1. You are most welcome. Having beaten myself up over this for ages too, it is nice to know I've helped end someone else's turmoil!

      Delete
  2. Thanks for this great post. Following the example solved my problem in about 5 minutes. MS Query will not show the join, but it works in Excel!

    ReplyDelete
  3. You are great!!!! Thank you so much!!

    ReplyDelete
  4. Why does "Microsoft Access" allow outer joins when multiple tables are in the query and "Microsoft Query" doesn't?

    ReplyDelete
    Replies
    1. I'm sorry, I have no idea why that should be so.

      Delete
  5. Ian - this is a great help! Thankyou.
    Andrew Hills

    ReplyDelete
  6. If You write the query in MS-Query as a derived table it will work perfectly with multiple outer joins.

    ReplyDelete
  7. Great! I was looking for this solution for couple months. Thanks!

    ReplyDelete