Share this content

Tutorial: MS Query and Sage #3 - Set up your analysis-ready file, By David Carter

17th Jan 2007
Share this content

It’s now time to get serious. Once you have used Microsoft Query to set up your own analysis-ready file in Excel, you will be able to use the power of pivot tables to produce pretty well any report you want. David Carter shows how.

In the last tutorial we brought over Sage Line 50 nominal balances into Excel. But if you are serious about improving your reports, you really need to bring over transactions at the detailed level. You can then use the power of pivot tables to produce all sorts of sophisticated summary reports.

If you want to bring over transactions from an accounts package, a good place to start is the Audit Trail report, because this report contains most detail at the transaction level. Sage has an on-screen audit trail report in the Financials module (Modules-Financials). Go to it now.

1. The Sage Financials screen
This is a nice-looking screen, and you can easily export it into Excel (File-Send To Excel).

However, as I explained in a recent article, the people who write accounts packages are mainly technicians who understand very little about reporting. This seems to be a particular problem with Sage. So many of its reports have something wrong with them when they get into Excel. Sometimes I think Sage does it deliberately.

For example, in this report Sage decided to make all amounts positive. So an invoice and a credit note for £100 apiece are both exported into Excel as 100.00 and all report totals are false. Another defect affects dates. They come over into Excel as text fields rather than proper dates, so you can’t group them to calculate monthly totals.

And essential fields are omitted too. So the screen shows code numbers for customers, suppliers, nominal accounts and departments, but without any accompanying descriptions these will be meaningless to most users. And while there are nominal account codes with which we can produce a trial balance, there are no nominal categories for producing a profit and loss report. And so on, and so on.

2. Create an “analysis-ready” file
In this tutorial we will take the on-screen audit trail as the basis, but correct the dates and numbers, make sure that code numbers always have a description, and add the grouping codes. At the end we will have constructed an “analysis-ready” file which can be used for producing reports.

It will take a little time, but the rewards will be great. For whenever a new transaction is entered into Sage, it will automatically be added to the analysis-ready file and every report in Excel will be dynamically updated.

3. Open the Demo company Stationery and Computer Mart UK
We’ll run the tutorial on the Sage’s demonstration company – Stationery and Computer Mart UK. This holds only a few thousand records and shouldn’t slow your machine down.

Having seen how it’s done, you can then set up an analysis-ready file on your own company data.

Remember that in order to do this exercise you must previously have set up MS Query and the Sage ODBC drivers as explained in the first article of the series. The second article introduced you to the basics of Query, and you should have worked through that too.

Start up Excel. Open a new workbook. Click onto cell A1.

From main menu choose (Excel 2003): Data–Import External Data–New Database Query
(Excel 2000 or Excel 97): Data–Get External Data–Create Database Query

The Choose Data Source box appears. [If it doesn’t, you need first to set up Query and Sage ODBC. Go through article one of this series].

Sage Demo Company is in the list of data sources. Highlight it and click OK.

In the Sage ODBC Connect screen, the UserID = MANAGER, Password – leave blank. OK.

The Query Wizard–Choose Columns dialogue box appears, with ACCRUAL, AUDIT HEADER etc visible. In the list of tables at the left, scroll down until you get to AUDIT JOURNAL. Click the + sign next to it.

4. The AUDIT_JOURNAL table
The 16 fields below are the ones I want to put into my analysis ready file. Twelve are contained in this AUDIT_JOURNAL table. However four of them (Period, AcctName, Nominal Name, Nominal Category) are stored in other tables.

..PERIOD [stored in the PERIOD table]
..ACCTNAME [stored in the PURCHASE_LEDGER table]
..NOMINAL NAME [stored in the NOMINAL_LEDGER table]
..NOMINAL CATEGORY [stored in the CATEGORIES table]

We will select the 12 now and add the other 4 later. Select the 12 fields by double clicking on them. They move into the right-hand box.

Make sure that all 12 fields are sorted into the same order as I’ve listed them above. (To move a field in the list, highlight it, then click on the up or down arrows next to the box).

Click Next. The Query Wizard-Filter Data screen appears. We don’t want to apply any filters at this stage, so click Next.

5. Apply SORT
The Query Wizard-Sort Order box appears. From the drop down select TRAN_NUMBER. Change the sort order from Ascending to Descending. Next. (Descending sort order means is that every time a new transaction is posted into Sage, it will appear at the top of the transaction list in Excel.)

The Query Wizard–Finish box appears. At top left it defaults to Return Data to Microsoft Excel. We want to do this now, so select Finish.

The Import Data box asks where we want to place the data. It should say: Existing worksheet = $A$1. Click OK.


It is very similar to the list on the Financials screen. Make sure that there are 12 columns and you have selected all the fields.

One early piece of good news: the Amounts in column H are both positive and negative.

6. Linking to other tables with “Joins”
We need 4 fields which were not held in the AUDIT_JOURNAL table. They are:
PERIOD [stored in the PERIOD table]
ACCTNAME [stored in the PURCHASE_LEDGER table]
NOMINAL NAME [stored in the NOMINAL_LEDGER table]

We’ll start with the NOMINAL NAME field. Right click to display the options menu and select Edit Query, which takes you into the Query wizard. Hit Next three times to get into the Finish box. Now choose View Data or Edit Query in Microsoft Query. Finish.

You see the Query screen with the data. Maximise the screen. Notice that in column A the Tran Number shows the latest transaction numbers first.

We are now going to make some joins. A big danger when you make joins is that you start to lose records and don’t notice it. We need to keep track of the number of records.

Click anywhere onto the data. Then go to the bottom of the file (Ctrl-Down Arrow). Make a note of the number of records at bottom left. My version says 3254 records.

Go back to the top of the file (Ctrl-Up Arrow).

7. Make a join to the NOMINAL_LEDGER table
The AUDIT_JOURNAL table is at top left. From main menu, select Table–Add Tables.

Find NOMINAL_LEDGER in the list. Highlight it, then double click to drop it down next to the AUDIT_JOURNAL table. The hourglass may run for a bit. Close.

In AUDIT_JOURNAL, scroll down to NOMINAL_CODE and highlight it.

With your left mouse button depressed, drag the mouse over the ACCOUNT_REF at the top of the NOMINAL_LEDGER table, then let go the mouse.

A line appears between NOMINAL_CODE and ACCOUNT_REF in the two tables, indicating that a join has been made.

In the NOMINAL_LEDGER table, scroll down to NAME.

Drag and drop NAME down to the right of the NOMINAL_CODE column heading. A blank column appears between NOMINAL_CODE and AMOUNT.

Now click the left exclamation mark icon at the top to run the query. The blank column fills with the names of the nominal accounts.

You must now check that you haven’t lost any records. Ctrl-Down Arrow to go the bottom of the file. There are still 3254 records. Good. Ctrl-Up Arrow to return to the top of the file.

8. Try to add the PURCHASE_LEDGER table
We’ve successfully added the Nominal Code description to the data. However, this is the only join that is going to work.

Let’s see what happens when we try to add the customer or supplier name to ACCOUNT_REF.

There is a difficulty here. Sage holds Customer names and details in one table (called SALES_LEDGER) and Supplier names and details in another (PURCHASE_LEDGER).

So any ACCTNAME field has to be populated from the SALES_LEDGER table if it is a sales invoice, and from the PURCHASE_LEDGER table if it is a purchase invoice.

Since it is (I think) more important to have the Supplier name than the Customer Name, we’ll try pulling down the PURCHASE_LEDGER table. Therefore:

Select: Table-Add Table). Scroll down the list of tables to find PURCHASE.LEDGER. Double click to select. It appears to the right of AUDIT.JOURNAL and NOMINAL.LEDGER. Close.

Before making the join, you need make more space in the top half of the screen. Click on the horizontal line at the top of the data and drag the data downwards.

Make a join between AUDIT_JOURNAL and PURCHASE_LEDGER. Use the ACCOUNTREF field in both tables.

Once you’ve made the join and there’s a line between the two tables, scroll down PURCHASE_LEDGER and find NAME. Drag and drop NAME to the right of the ACCOUNT_REF column heading.

A blank column is inserted. Click the left exclamation icon to run the query. The blank column is populated with names, so everything seems to have worked.

However, now use Ctrl-Down Arrow to check the number of records at the bottom of the file. On my version it says that there are now only 875 records in the database whereas it started with 3254.

Query has removed many transactions, for example all the journals. Although this join seemed to have worked successfully, it didn’t. You have to be very careful when making joins.

We will delete this new NAME column. Highlight the column by clicking on the NAME column heading, then press the DELETE key. Left Exclamation mark to run query.

Ctrl-Down Arrow again to go the end of the file. There are still only 875 records. We have to remove the PURCHASE_LEDGER table completely to restore all the transactions.

Click anywhere on the PURCHASE_LEDGER table. Then from the main menu chose Table – Remove Table.

Click the left Exclamation mark icon to run the query. Now check how many records there are.

9. No joins possible for the PERIOD and CATEGORY Tables
When preparing this tutorial I tried making joins for the PERIOD and CATEGORY tables. Neither worked. So don’t bother to try either of these yourself. For the record, PERIOD did seem to work, but the period numbers were wrong. CATEGORY just set the hour glass running for the next 20 minutes until I had to crash out with Ctrl-Alt Delete.

So, we’ve managed to insert only 1 of the 4 extra fields we need. Not so good, but there is still hope. For the time being, however, move on to the next section.

10. Filter out deleted records
Sage allows you to amend incorrect transactions. To maintain the audit trail, Sage creates a new (corrected) transaction, but it also keeps the original faulty transaction on file.

Sage flags this faulty transaction as Deleted and excludes it from any reports. We also need to exclude any deleted transactions. To do this, from main menu, select Criteria–Add Criteria.

In the Field box, scroll down the drop down list and select the AUDIT_JOURNAL.DELETED field.

In the Operator box, the value should be “equals”.

In the Values box type: 0 (zero).

Now click on Add.

A new box appears across the centre of the screen. At the left it says: Criteria field DELETED FLAG Value 0

Click on Close to close the box. Then click on the left exclamation icon.

Query searches out and removes any Deleted transactions (I don’t think there are any Deleted transactions in the Demonstration company, but there may be some in your own system. A Deleted record has value = 1, a live record has value = 0.)

11. Filter out balance sheet accounts
Personally, I like to get rid of balance sheet records. There is little point in storing thousands of Debtors Control and Creditors Control records and most of the reports I write relate to the P&L.

So from main menu, select Criteria – Add Criteria.

In the Field box, click on the drop down list and select AUDIT_JOURNAL.NOMINAL_CODE.

In the Operator box, change “equals” to “is greater than or equal to”.

Now click the Values button at bottom right. From the list of account codes, select 4000. OK. Now click on Add.

A new box appears across the centre of the screen. At the left it says: Criteria field NOMINAL_CODE, Value >= [4000].

Close. Then click on the left exclamation icon to run the query.

An Enter Parameter Value box appears, suggesting 4000. Type in 4000. OK. The previous nominal codes disappear and now the list starts with 4000.

To see how many transactions have been removed, click on the data and Ctrl-Down Arrow. My version says there are now 1016 records.

12. Change the column headings.
Finally, we’ll tidy up the column headings. Double click on the column heading TRAN_NUMBER. The column is highlighted and the Edit Column box appears.

Click onto the second (empty) box – Column Heading.

Type TRAN and click OK. TRAN is the new heading, but the column may go blank. If it does, click the exclamation icon).

Now set the column headings as follows (some don't need to be changed):


Left exclamation icon to show all the data.

Now save the query. From main menu choose: File–Save As. Change the file name to: Query from Sage DemoCo analysis ready.

Now select File–Cancel and Return to MS Office Excel.

The column headings haven't changed. We will have to re-run the query, so close down Excel WITHOUT saving.

14. Re-start the Query
Start up Excel. Open a new workbook. Click onto cell A1. From main menu (Excel 2003): Data–Import External Data–Import Data.
(Excel 97 or 2000): Data–Get External Data–Run Database Query.

The Select Data Source box appears. In the list of My Data Sources, highlight Query from Sage DemoCo analysis ready. Open. OK.

Import Data box – OK. Type in 4000 Click the “use this value/reference for future refreshes” box.

The data appears. It’s tidier with the new column headings, but we are still missing those 3 extra fields. But even though we can’t get at them in Query, we can have a second bite at the cherry and try getting hold of them in Excel. We will do this in the next article.

In the meantime, however, even without all the data fields it is still possible to see the potential benefits of “live linking” with ODBC and Query. We’ll finish by setting up a pivot table in Excel. Then we’ll go into the Sage Demonstration company, enter a purchase invoice, and see what happens.

15. Set up a pivot table by department
Firstly, find the tab name at bottom left (it’s probably Sheet1). Double click on it, and rename this worksheet MASTER.

Now click anywhere on the data. From the main menu select: Data – Pivot Table and PivotChart report.

The Pivot Table Wizard, Step 1 of 3, screen appears. Click on Next

Step 2 of 3 The Range box should say: $A$1:$M$1017 or similar. Click on Next.

Step 3 of 3: Click on Layout

The COLUMN-ROW-DATA box appears. To the right are the column headings of the spreadsheet

Drag and drop these fields from the list.

DEPT_NAME into the COLUMN area
NOMCD into the ROW area
NOMNAME into the ROW area below NOMCD
AMOUNT into the DATA area

Now click on OK, then Finish

The Pivot Table is created in a new worksheet.

Excel has automatically created subtotals for NOMCD, which make the screen difficult to read. To remove them:

Place your mouse pointer over the grey field button “NOMCD” in cell A4.

Double left click the mouse. The “Pivot Table Field” box appears.

In “Subtotals” at the left, change from Automatic to None. OK The subtotals are removed.

Finally, tidy up the numbers. Format them all to two decimal places, and make negative numbers red.

This pivot table is handy if you analyse costs by department. The first column contains items that haven’t been given a department code. You can check if this is correct.

16. Enter a new purchase invoice into Sage
Looking at this pivot table, cell E11 contains the value of Materials Purchased (Nominal code 5000) by the Installations department. Right now it is blank, i.e. zero.

We are going to enter a purchase invoice for £10,000 into Sage and analyse it to the Materials Purchased account, and the Installations department.

Minimise Excel. Then start up Sage. Go into the demo company Stationery and Computer Mart. (File – Open – Open Demo Data – MANAGER)

From main menu: Modules – Suppliers – Invoice. Choose any supplier, make the reference fields anything you like, N/C = 5000, Dep = 6, Details = “my new invoice”, Net = 10000, VAT=1750. Save.

17. See the new invoice in Excel
Now switch back into Excel. Go into the MASTER worksheet.

Nothing has changed yet. Right click for menu. At the bottom select: Refresh Data. OK.

Your new invoice is added at the top of the screen.

Now go into the pivot table worksheet. Nothing has changed.

Right click on the data for menu. Refresh Data. The 10000 now appears in cell E11.

Double click on E11. Excel generates a drill down sheet showing you details of the new invoice.

That’s OK for now. Close down, and save the Excel file as: analysisready.xls.


Previous tutorials in the Sage & MSQuery series

  • Tutorial MSQuery and Sage #1:How to Set up Import External Data
  • Tutorial MSQuery and Sage #2: Import Balances into Excel
  • Tutorial: MSQuery and Sage #4: Add the missing pieces of data
  • Tutorial: MSQuery and Sage #5 - Add nominal categories
  • Related material
    David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:

  • Reporting Tools #4: Introduction to summary reporting
  • Towards an analysis-ready file for reporting #1
  • Towards an analysis-ready file for reporting #2
  • Want to learn more about pivot tables? Start here
  • Improve your reporting skills with self-teach tutorials
  • An introduction to Excel-driven reporting tools
  • Subscribe to the ExcelZone newswire
    To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

    Replies (10)

    Please login or register to join the discussion.

    By David Carter
    02nd Feb 2007 11:12

    no, it's Audit Journal
    Hi JC

    No, Sage tell me it's Audit_Journal. I agree that on the face of it the detail record would seem to be the right one, but Sage wasn't written with detail and header records - just detail records. That's why they don't have a proper screen for purchase invoice entry. If you want to analyse one invoice to two GL accounts you have to enter two invoices.

    It's pretty confusing. They've got 4 tables called Audit_Journal, Audit_Split, Audit_Header and Audit_History - all virtually identical!

    I think it's because Sage was originally an accounting shoe-box system and they've had to bend it a lot over the years.

    Thanks (0)
    By David Carter
    22nd Jan 2007 11:32

    what about the Purchase Ledger table?
    JC, hi
    Thanks for that. Does the fact that you've left out the PURCHASE_LEDGER table mean that it is impossible to join it in without additional programming?

    I've been fiddling around with right-outer joins and things but can't get it to work.

    Thanks (0)
    By Anonymous
    22nd Jan 2007 20:14

    Are we using the right table .......
    have looked at this matter slightly more in depth

    This quite an interesting one because one would assume that the AUDIT_SPLIT table is equivalent to the GL; therefore if all items were signed the sum of all items in this table would be zero (double entry). In order to lift the P/L items we need to select everything with a Nominal >= 4000; any items with a code below this figure are B/S items

    It very much looks as though the AUDIT_JOURNAL is simply a breakdown of the child items identified by the AUDIT_HEADER record (one to many relationship) and as such may not be the right table to work with; what happens when the Audit Trail is cleared down?

    If this is the case we probably need to be working on the AUDIT_SPLIT table

    My guess is that you need to extract data from the AUDIT_SPLIT table because that will remain complete under all circumstances.

    Try the following


    You will see that the field AUDIT_SPLIT.ACCOUNT_REF is used for 3 purposes depending upon the transaction type contained in AUDIT_SPLIT.TYPE
    - NOMINAL_LEDGER second time; therefore needs to be aliased in the sql (i.e. NOMINAL_LEDGER_1)

    I haven't bothered to filter the selection with Nominal code >= 4000 or deleted records but this should not be an issue to insert at a later date.

    The above SQL links the relevant tables and overcomes the initial problem of linking PURCHASE_LEDGER

    Have a play with the SQL statement - what do you think??

    Thanks (0)
    By Anonymous
    18th Jan 2007 18:24

    Complimentary Approach ....

    Whilst I am not a fan of Sage, in fairness there are reasons for reporting deficiences and unsigned values. On the report front users do not really have defined uniform requirements; everyone wants to put their own slant on reporting which is very difficult to cater for. Unsigned figures are identified by an associated transaction type code because the sign may different depending upon how they are used (i.e. GL & Bank figures)

    By using ordered Nominal Codes such as >= 4000 Sage are being very prescriptive. In these days of intuitive systems the question is 'why are alpha/numeric codes used at all'? How about meaningful descriptions which are in turn mapped to codes behid the scenes against a balance sheet type table. This would make it very easy to extract P/L etc information simply on BS_Type. Sage approach is primative to say the least

    I have left out PURCHASE_LEDGER table but here is the rest of the article using Access & ODBC to link to Sage data. Simply copy and paste the following into Access Query designer SQL area


    Depending upon circumstances Access may sometimes be more appropriate tool for manipulating data

    Thanks (0)
    By Anonymous
    07th Feb 2007 10:01

    Try this .....
    Sorry missed the reply of 02 Feb 2007 - for some reason the AWeb notification is very hit & miss. Sometimes (intermitently) it completely overlooks postings and does not notify other contributors on the thread

    Anyway in the light of Sage advice in that posting

    Category is dependant upon the Chart of Accounts; therefore if multiple COA's exist one cannot really link to Category without first defining the selected COA. Provided the COA is known you can do a sub-query to determine Category based upon COA number and Account Range, however, that gets into the 'advanced SQL' arena & is probably outside the scope of your tutorial

    Use both Sales & Customer ledger names as per the following SQL. Use Nominal Name descriptions as per SQL. If results are exported to Excel you can always combine the resulting 3 columns into one. They are mutually exclusive and any occurance will only appear in one of the columns per record

    You dont really need the Period table - all it seems to do is allocate months to periods and this can be achieved in SQL (i.e. YEAR([DATE]) & FORMAT(MONTH([DATE]),"00") = 200702; last column in SQL

    SQL as follows:

    Thanks (0)
    By stuart01
    20th Apr 2007 17:54

    Analysis ready tutorial

    I've been following this, but wondered if you were going to complete the series?

    Many thanks,


    Thanks (0)
    By David Carter
    29th Apr 2007 09:26

    next article?
    Sorry, Stuart., I've got sidetracked onto other topics. Now you've reminded me I'll try to finish it off in the next couple of weeks.

    {Nag #2. Yes, Stuart, it is.]

    Thanks (0)
    By stuart01
    22nd Jun 2007 17:50

    Sage reporting
    Don't mean to nag, but is this still on the radar?

    Thanks (0)
    By David Carter
    23rd Aug 2007 12:15

    finally done
    If you're still there, the series continues today 21st August. Apologies.

    Thanks (0)
    By themowgli
    03rd Aug 2010 10:12

    Sage Analysis File

    Hi, I know this was done several years ago.  I took alot of time to prepare an analysis ready file with the expert tutorial provided.  However, when I upgraded to Sage 2010 my analysis file is no longer working.  How do you make the transition without having to redo the whole exercise? Thanks 

    Thanks (0)