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

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.

Continued...

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

Comments

no, it's Audit Journal

David Carter | | Permalink

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.

what about the Purchase Ledger table?

David Carter | | Permalink

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.

Are we using the right table .......

Anonymous | | Permalink

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

SELECT NOMINAL_LEDGER.NAME, PURCHASE_LEDGER.NAME, SALES_LEDGER.NAME, NOMINAL_LEDGER_1.NAME, AUDIT_SPLIT.*
FROM (((AUDIT_SPLIT LEFT JOIN NOMINAL_LEDGER ON AUDIT_SPLIT.NOMINAL_CODE = NOMINAL_LEDGER.ACCOUNT_REF) LEFT JOIN PURCHASE_LEDGER ON AUDIT_SPLIT.ACCOUNT_REF = PURCHASE_LEDGER.ACCOUNT_REF) LEFT JOIN SALES_LEDGER ON AUDIT_SPLIT.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF) LEFT JOIN NOMINAL_LEDGER AS NOMINAL_LEDGER_1 ON AUDIT_SPLIT.ACCOUNT_REF = NOMINAL_LEDGER_1.ACCOUNT_REF
ORDER BY AUDIT_SPLIT.NOMINAL_CODE;

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
- PURCHASE_LEDGER
- SALES_LEDGER
- 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??

Complimentary Approach ....

Anonymous | | Permalink

David

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

SELECT AUDIT_JOURNAL.TRAN_NUMBER, AUDIT_JOURNAL.TYPE, AUDIT_JOURNAL.DATE, AUDIT_JOURNAL.DATE_ENTERED, AUDIT_JOURNAL.ACCOUNT_REF, AUDIT_JOURNAL.DETAILS, AUDIT_JOURNAL.NOMINAL_CODE, NOMINAL_LEDGER.NAME, AUDIT_JOURNAL.AMOUNT, AUDIT_JOURNAL.DEPT_NUMBER, AUDIT_JOURNAL.DEPT_NAME, AUDIT_JOURNAL.INV_REF, AUDIT_JOURNAL.EXTRA_REF, AUDIT_JOURNAL.DELETED_FLAG
FROM AUDIT_JOURNAL INNER JOIN NOMINAL_LEDGER ON AUDIT_JOURNAL.ACCOUNT_REF = NOMINAL_LEDGER.ACCOUNT_REF
WHERE (((AUDIT_JOURNAL.NOMINAL_CODE)>="4000") AND ((AUDIT_JOURNAL.DELETED_FLAG)=0))
ORDER BY AUDIT_JOURNAL.TRAN_NUMBER DESC;

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

Try this .....

Anonymous | | Permalink

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:
--------------------
SELECT AUDIT_JOURNAL.TRAN_NUMBER, AUDIT_JOURNAL.TYPE, AUDIT_JOURNAL.DATE, AUDIT_JOURNAL.ACCOUNT_REF, SALES_LEDGER.NAME AS CustomerName, PURCHASE_LEDGER.NAME AS SupplierName, NOMINAL_LEDGER_1.NAME AS NominalName, AUDIT_JOURNAL.DETAILS, AUDIT_JOURNAL.NOMINAL_CODE, NOMINAL_LEDGER.NAME, AUDIT_JOURNAL.AMOUNT, AUDIT_JOURNAL.DEPT_NUMBER, AUDIT_JOURNAL.DEPT_NAME, AUDIT_JOURNAL.INV_REF, AUDIT_JOURNAL.EXTRA_REF, AUDIT_JOURNAL.DELETED_FLAG, Year([DATE]) & Format(Month([DATE]),"00") AS YearMth FROM (((AUDIT_JOURNAL LEFT JOIN NOMINAL_LEDGER ON AUDIT_JOURNAL.NOMINAL_CODE = NOMINAL_LEDGER.ACCOUNT_REF) LEFT JOIN SALES_LEDGER ON AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF) LEFT JOIN PURCHASE_LEDGER ON AUDIT_JOURNAL.ACCOUNT_REF =
PURCHASE_LEDGER.ACCOUNT_REF) LEFT JOIN NOMINAL_LEDGER AS NOMINAL_LEDGER_1 ON AUDIT_JOURNAL.ACCOUNT_REF = NOMINAL_LEDGER_1.ACCOUNT_REF WHERE (((AUDIT_JOURNAL.NOMINAL_CODE)>="4000") AND
((AUDIT_JOURNAL.DELETED_FLAG)=1))
ORDER BY AUDIT_JOURNAL.TRAN_NUMBER DESC;

Analysis ready tutorial

stuart01 | | Permalink

David,

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

Many thanks,

Stuart

next article?

David Carter | | Permalink

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

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

Sage reporting

stuart01 | | Permalink

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

finally done

David Carter | | Permalink

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

Sage Analysis File

themowgli | | Permalink

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