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

Kashflow logo
Share this content

As 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...

Please Login or Register to read the full article

The full article is available to registered members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About AccountingWEB


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)
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)
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)

Related content