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

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

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.


» Register now

The full article is available to registered AccountingWEB 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.


Is there a general data table map available?

mbax | | Permalink

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.

It's not that simple

David Carter | | Permalink

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!