Tim Richardson

In this article, I'm going to build an Excel report to query AR; that is, a pivot table aging report.

The more advanced techniques demonstrated here are

a) connecting to accpac from within an Excel macro

b) using an Accpac view to safely update the Accpac database

Pivot tables are a great reporting tool. In this case, we have our customers grouped into sales channels, so the pivot table starts by summarising per sales channel, and allows drill down to customer. Another click lets the user drill down into invoice detail. We also show credit limit, terms, last payment date. We have the same format for AP with the same drill-down capabilities.

I will assume the reader knows basic Excel macros.

Accpac does all the hard work of aging: there is a temporary table which very neatly ages all open items. However, we can't simply query this table in Excel using the SQL approach I explained in part 1, because the temporary table needs to be updated first; this aging table is not updated whenever something changes in AR. What we do is get Accpac to do the work, and this can be done from within Excel, via a macro which connects to Accpac.Accpac bundles program logic into "views"; updated AR is a certain view, and the job of our macro is to run that view.

Working out which view you want is pretty easy. I recorded a macro while running the existing AR Aging report, and in the code generated, I learnt how to invoke the view. Recording macros in Accpac is not the same as running macros in Excel; hopefully this tutorial will bridge the gap.

This was developed on Accpac 5.5A.

I'm going to store here a scrapbook or cookbook for Accpac SQL Queries which may be useful.

See here about Accpac SQL queries and Excel

In my Accpac review I mentioned that I avoided using Crystal Reports. Instead, most of my queries and reports have been Excel spreadsheets. Typically, one tab is populated with data from Accpac, and then the report pages are pivottables. This allows users to drill down, summarise and work with data in a familiar environment. Basic queries also avoid any additional licences.

In this article, I will show an example: a report analysing cash transactions against bank accounts.

In next article, I will show a more advanced example: an aged receivables report, which uses an Excel macro which first connects to Accpac to update the aging tables, and then queries the result. This is a very interesting example because it shows the true power of automating Accpac with Visual Basic for Applications, a simple technology available to any one with Microsoft Office. 

A lot of Australian SMEs use traditional MYOB products, such as MYOB Premier. MYOB has a few weaknesses, but it's well known, well supported and handles basic accounting quite well. In fact, it handles it so well that there is a lot more data in MYOB than you can report on. A huge weakness of MYOB is the poor reporting.

Another weakness of MYOB is its treatment of historical data. Many MYOB sites will have historical data kept in old, archived databases.A technique that allows access to this data is interesting.

This is a tutorial about accessing data from MYOB Premier using SQL.This technique opens up enormous possibilities to do interesting things with MYOB data, as well as making data migration into a new system much easier, and it helps tremendously with ATO GST Audits (I speak from experience).

I'm going to assume that the reader has some basic SQL. In this tutorial, I'll cover basic queries; you can use Microsoft Query and Excel. If you don;t know what ODBC is and how to query data into Excel, you'll want to learn that and come back here.