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

Monday, 3 September 2012

Convert Crosstab to List

Sometimes the only information available to you is in crosstab format whereas, for whatever reason, you really need the information in list format.  This post describes a gereric approach to converting a crosstab to a list in Excel 2003. The data used for the example is the sort of thing you might get if someone copied a pivottable to a separate workbook and didn't include the underlying data.  For simple crosstabs there are other, simpler, ways of getting this done, but this approach should work for even quite complex crosstabs.

Here's the basic data as received:

20092009 Total
SalesProduct Sales4000 - Sales North(39,832.70)(37,076.73)(39,705.77)(47,349.18)(163,964.38)
4001 - Sales South                 -           100.00                  -      (1,330.00)     (1,230.00)
4002 - Sales Scotland                 -         (100.00)      (102.51)          50.00         (152.51)
4009 - Discounts Allowed          50.00                  -                    -                    -               50.00
Product Sales Total(39,782.70)(37,076.73)(39,808.28)(48,629.18)(165,296.89)
Other Sales4900 - Miscellaneous Income                 -                    -           (55.00)           (5.03)           (60.03)
4905 - Distribution and Carriage      (200.00)      (200.00)      (200.00)      (270.00)        (870.00)
Other Sales Total      (200.00)      (200.00)      (255.00)      (275.03)        (930.03)
Sales Total(39,982.70)(37,276.73)(40,063.28)(48,904.21)(166,226.92)
Purchases5000 - Materials Purchased    3,713.90   19,062.54     7,845.83   14,824.21     45,446.48
5001 - Materials Imported                 -       4,023.00     7,159.00   12,551.00     23,733.00
5002 - Miscellaneous Purchases                 -                    -                    -       1,136.53        1,136.53
Purchases Total    3,713.90   23,085.54   15,004.83   28,511.74     70,316.01
Purchase Charges5100 - Carriage                 -                    -                    -               1.26                1.26
Purchase Charges Total                 -                    -                    -               1.26                1.26
Purchases Total    3,713.90   23,085.54   15,004.83   28,513.00     70,317.27

First, we need to make sure we have all the necessary row and column data available. We need to fill in the blanks in the Category and Sub_Category rows and the Year column.

Select A4 to C19, press F5 (Edit | Go To...), left click Special..., left click the Blanks radio button and then left click on OK.  Next, press "=", press the Up arrow once and then press
Ctrl+Enter.  Now select D2 to H3, press F5 (Edit | Go To...), left click Special..., left click the Blanks radio button and then left click on OK.  Then, press "=", press the Left arrow once and then press Ctrl+Enter.  All the blank cells on detail rows and columns should now have the appropriate labels on them (don't worry about the Total rows and columns - we'll discard this data later).

The data now looks like this:

220092009200920092009 Total
4SalesProduct Sales4000 - Sales North(39,832.70)(37,076.73)(39,705.77)(47,349.18)(163,964.38)
5SalesProduct Sales4001 - Sales South                 -           100.00                  -      (1,330.00)     (1,230.00)
6SalesProduct Sales4002 - Sales Scotland                 -         (100.00)      (102.51)          50.00         (152.51)
7SalesProduct Sales4009 - Discounts Allowed          50.00                  -                    -                    -               50.00
8SalesProduct Sales Total4009 - Discounts Allowed(39,782.70)(37,076.73)(39,808.28)(48,629.18)(165,296.89)
9SalesOther Sales4900 - Miscellaneous Income                 -                    -           (55.00)           (5.03)           (60.03)
10SalesOther Sales4905 - Distribution and Carriage      (200.00)      (200.00)      (200.00)      (270.00)        (870.00)
11SalesOther Sales Total4905 - Distribution and Carriage      (200.00)      (200.00)      (255.00)      (275.03)        (930.03)
12Sales TotalOther Sales Total4905 - Distribution and Carriage(39,982.70)(37,276.73)(40,063.28)(48,904.21)(166,226.92)
13PurchasesPurchases5000 - Materials Purchased    3,713.90   19,062.54     7,845.83   14,824.21     45,446.48
14PurchasesPurchases5001 - Materials Imported                 -       4,023.00     7,159.00   12,551.00     23,733.00
15PurchasesPurchases5002 - Miscellaneous Purchases                 -                    -                    -       1,136.53        1,136.53
16PurchasesPurchases Total5002 - Miscellaneous Purchases    3,713.90   23,085.54   15,004.83   28,511.74     70,316.01
17PurchasesPurchase Charges5100 - Carriage                 -                    -                    -               1.26                1.26
18PurchasesPurchase Charges Total5100 - Carriage                 -                    -                    -               1.26                1.26
19Purchases TotalPurchase Charges Total5100 - Carriage    3,713.90   23,085.54   15,004.83   28,513.00     70,317.27

Now we need to create a version of this crosstab with only one row field and one column field.  We'll use the tilde character as a delimiter so we can recover the original row and column data later.  In I4 type "=A4&"~"&B4&"~"&C4" and press enter. You should see Sales~Product Sales~4000 - Sales North in I4.  Copy the formula in I4 all the way down to I19. Then type "=D2&"~"&D3" in cell J3.  You should see 2009~Jan-09 in J3.  Copy the formula in J3 all the way across to N3.  Now type "=D4" in cell J4 and copy the formula all the way to N19.  This is what you should have in I3 to N19:
2009~Jan-092009~Feb-092009~Mar-092009~Apr-092009 Total~Apr-09
Sales~Product Sales~4000 - Sales North   (39,832.70)   (37,076.73)    (39,705.77)   (47,349.18)           (163,964.38)
Sales~Product Sales~4001 - Sales South                   -             100.00                      -        (1,330.00)                (1,230.00)
Sales~Product Sales~4002 - Sales Scotland                   -            (100.00)          (102.51)             50.00                    (152.51)
Sales~Product Sales~4009 - Discounts Allowed            50.00                     -                        -                       -                          50.00
Sales~Product Sales Total~4009 - Discounts Allowed   (39,782.70)   (37,076.73)    (39,808.28)   (48,629.18)           (165,296.89)
Sales~Other Sales~4900 - Miscellaneous Income                   -                       -               (55.00)             (5.03)                      (60.03)
Sales~Other Sales~4905 - Distribution and Carriage        (200.00)         (200.00)          (200.00)         (270.00)                   (870.00)
Sales~Other Sales Total~4905 - Distribution and Carriage        (200.00)         (200.00)          (255.00)         (275.03)                   (930.03)
Sales Total~Other Sales Total~4905 - Distribution and Carriage   (39,982.70)   (37,276.73)    (40,063.28)   (48,904.21)           (166,226.92)
Purchases~Purchases~5000 - Materials Purchased       3,713.90      19,062.54         7,845.83      14,824.21                45,446.48
Purchases~Purchases~5001 - Materials Imported                   -          4,023.00         7,159.00      12,551.00                23,733.00
Purchases~Purchases~5002 - Miscellaneous Purchases                   -                       -                        -          1,136.53                   1,136.53
Purchases~Purchases Total~5002 - Miscellaneous Purchases       3,713.90      23,085.54       15,004.83      28,511.74                70,316.01
Purchases~Purchase Charges~5100 - Carriage                   -                       -                        -                  1.26                           1.26
Purchases~Purchase Charges Total~5100 - Carriage                   -                       -                        -                  1.26                           1.26
Purchases Total~Purchase Charges Total~5100 - Carriage       3,713.90      23,085.54       15,004.83      28,513.00                70,317.27
Select Data | PivotTable and PivotChart Report... | Multiple Consolidation Ranges and then click on Next.  Then select I will create the page fields and click on Next. Select cells I3 to N19 and click on Next. Select New worksheet and click on Finish. Double click on the grand total cell in the newly created PivotTable to extract what it sees as the underlying data in list format.  The data extracted should start off looking like this:
Purchases Total~Purchase Charges Total~5100 - Carriage2009 Total~Apr-0970317.27
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Apr-0928513
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Feb-0923085.54
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Jan-093713.9
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Mar-0915004.83
Purchases~Purchase Charges Total~5100 - Carriage2009 Total~Apr-091.26
Purchases~Purchase Charges Total~5100 - Carriage2009~Apr-091.26
Purchases~Purchase Charges Total~5100 - Carriage2009~Feb-090
Purchases~Purchase Charges Total~5100 - Carriage2009~Jan-090
Purchases~Purchase Charges Total~5100 - Carriage2009~Mar-090
Purchases~Purchase Charges~5100 - Carriage2009 Total~Apr-091.26
Purchases~Purchase Charges~5100 - Carriage2009~Apr-091.26
Purchases~Purchase Charges~5100 - Carriage2009~Feb-090
Purchases~Purchase Charges~5100 - Carriage2009~Jan-090
Purchases~Purchase Charges~5100 - Carriage2009~Mar-090
In D2 enter =A2&"~"&B2. Copy this all the way down to the last row of data. Select column D and Edit | Copy | Paste Special... | Values and click OK. Activate Data | Filter | Autofilter  on Column D and set the filter to only show rows that contain the word Total. Select all rows of data from row 2 to the end and right click Delete Row. Remove the autofilter. Select column D again. Then select Data |Text to Columns..., click on Delimited and click Next, put a ~ in the box beside Other and click Finish. To record the field names copy A3 to C3 in the original crosstab to D1 to F1, and D1 to E1 in the original crosstab to G1 to H1. Enter Amount in C1 (the original title of the value column in the crosstab). Delete Columns A and B. If you wish to remove some superfluous data you can filter on the Amount column to isolate zero values and delete those rows.  Here's the final tidied up data:

1.26PurchasesPurchase Charges5100 - Carriage2009Apr-09
14824.21PurchasesPurchases5000 - Materials Purchased2009Apr-09
19062.54PurchasesPurchases5000 - Materials Purchased2009Feb-09
3713.9PurchasesPurchases5000 - Materials Purchased2009Jan-09
7845.83PurchasesPurchases5000 - Materials Purchased2009Mar-09
12551PurchasesPurchases5001 - Materials Imported2009Apr-09
4023PurchasesPurchases5001 - Materials Imported2009Feb-09
7159PurchasesPurchases5001 - Materials Imported2009Mar-09
1136.53PurchasesPurchases5002 - Miscellaneous Purchases2009Apr-09
-5.03SalesOther Sales4900 - Miscellaneous Income2009Apr-09
-55SalesOther Sales4900 - Miscellaneous Income2009Mar-09
-270SalesOther Sales4905 - Distribution and Carriage2009Apr-09
-200SalesOther Sales4905 - Distribution and Carriage2009Feb-09
-200SalesOther Sales4905 - Distribution and Carriage2009Jan-09
-200SalesOther Sales4905 - Distribution and Carriage2009Mar-09
-47349.18SalesProduct Sales4000 - Sales North2009Apr-09
-37076.73SalesProduct Sales4000 - Sales North2009Feb-09
-39832.7SalesProduct Sales4000 - Sales North2009Jan-09
-39705.77SalesProduct Sales4000 - Sales North2009Mar-09
-1330SalesProduct Sales4001 - Sales South2009Apr-09
100SalesProduct Sales4001 - Sales South2009Feb-09
50SalesProduct Sales4002 - Sales Scotland2009Apr-09
-100SalesProduct Sales4002 - Sales Scotland2009Feb-09
-102.51SalesProduct Sales4002 - Sales Scotland2009Mar-09
50SalesProduct Sales4009 - Discounts Allowed2009Jan-09
This data is good to go for whatever sort of different analysis you wish to do with it.
With a bit of planning you can set the basic formulae up in such a way that they'll work with any crosstab you paste into the worksheet (within maximum row and column field number limits you decide upon).
Comments or suggestions for improvement welcome