Share this content

Reporting tutorials: Use MS Query to extract and analyse accounts data

1st Feb 2008
Share this content

David Carter, Consultant editorAs part of an on-going campaign to help accountants and finance managers produce better reports from their transactional data, AccountingWEB consultant editor David Carter has produced a series of tutorial articles explaining how Microsoft Query can help you get extract and format data from an accounts system into an "analysis-ready file".

"The people who write accounts packages are mainly technicians who understand very little about reporting," he says. But once you have managed to locate the source data and get it into Excel in a clean condition, "You will be able to use the power of pivot tables to produce pretty well any report you want. These tutorials show you how."

Due to its popularity, the Stationery Exhibitions demo company in Sage Line 50 was used for the initial series on how to locate and extract the data using ODBC (open database connectivity). But the same principles can be applied to any other ODBC-compliant accounts system. Further tutorials are listed below for non-Sage users.

Tutorial 1: Using MS Query to get Sage data into Excel
Microsoft Query only works with packages that are ODBC-enabled. First you will need to ensure that Query is installed within Excel and that the Sage ODBC drivers are correctly set up. It can be a fiddly operation, but should take just a few minutes. If you do have problems, persevere: it’s only a one-off job, and when ODBC is finally set up, you will have access to your data for evermore.

Tutorial 2: Import Sage balances into Excel with MS Query
Sage offers numerous Trial Balance and P&L reports - but only allows you to print a TB or P&L for a single period. This tutorial uses Query to extract the nominal account balances for several months from Sage and paste them into Excel. The tutorial also covers how to format the results so they appear the way you want in your spreadsheet.

Tutorial 3: Set up your analysis-ready file
If you are serious about improving your reports, you really need to bring over transactions at the detailed level, so you can then use pivot tables to produce different summary reports. The Audit Trail report is a good place to start, because it contains most detail at the transaction level - but not all the fields you want. The tutorial tackles formatting issues, and attempts to retrieve the missing data.

Tutorial 4: Add the missing pieces of data
When you get data out of an accounts package into Excel, key items of data are usually missing. How do you include them? This tutorial explains how to use Excel's Vlookup function to pull in the extra data from a lookup table.

Tutorial 5: Add nominal categories
Following on from the previous tutorial, David Carter now introduces two more new fields into the Sage analysis-ready file – the nominal categories. Having brought them in, we can now produce a Profit and Loss report.

Tutorial 6: Add customer and supplier names
When you export data out of Sage, it includes account codes of customers and suppliers, but not their names. This may not matter for your own use, but any report for a manager has to include full names and descriptions. The final tutorial in this series, shows how to add customer and supplier names to the analysis-ready file.

Tutorial 7: Use MS Query to create an analysis-ready SALES file in Sage
If you use Sage for sales invoicing, this will give you the data you need to report on sales and margins by product and customer. This tutorial shows you how to use MS Query to pull in data from the invoice data tables.

Import External Data tutorials for other accounting applications
Import Data from Navision into Excel
Import data from Pegasus Opera into Excel
Management Reporting in QuickBooks
Import data from TAS Books into Excel
Import data from Iris Exchequer into Excel. Part 1
Import data from Iris Exchequer into Excel. Part 2
Import data from MYOB into Excel. Part 1
Import data from MYOB into Excel. Part 2
Import data intoExcel from Access Dimensions. Part 1
Access Dimensions and Excel, part 2 - Access Analytics
Import data from Sage Line 100 into Excel
Import data from Sage Line 200 into Excel

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
Management Reporting in Sage Line 50 and Instant
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 (3)

Please login or register to join the discussion.

By mbax
08th Feb 2008 14:37

Is there a general data table map available?
I use MS Query extensively and it is a great tool for enabling data extraction and manipulation in a spreadsheet environment.

My main use for Query was to extract data from a Dynamics NAV database but more and more often I am being asked to look at other systems such as Access and Sage for example.

Due to the time spent on the Dynamics database I have a detailed understanding of the basic data tables in the database but am not so familiar with others. I was wondering if anyone knew if such data maps were available without having to purchase the individual products. I understand that many databases are customised for individual implementations but I would have thought that the core tables, general ledger postings, stock entries etc. would be fairly standard.

Thanks (0)
By David Carter
11th Feb 2008 09:43

It's not that simple
Package vendors obviously do publish record layouts for their data tables. They will supply them to their resellers who need them for customisation etc.

However they are usually reluctant to give them to end-users. So if you are working with a client your best bet is to badger the reseller into giving you a copy.

On the other hand, in most cases you are simply seeing what the programmers see, so there's no guarantee they will mean anything! The field names are often incomprehensible and inconsistent. For example, Sage is pretty good but you still find that ACCOUNT_REF means different things in different tables.

It's a minefield out there!

Thanks (0)
By jonesms0n
30th Jan 2018 22:31

Hello, the links for tutorials 1 and 2 do not seem to be valid anymore. Could you please update and let me know? Thanks,

Thanks (0)