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

Kashflow logo
10

Its 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. You can then use the power of pivot tables to produce all sorts of sophisticated summary reports.

If you want to bring over transactions from an accounts package, a good place to start is the Audit Trail report, because this report contains most detail at the transaction level. Sage has an on-screen audit trail report in the Financials module (Mo...

Please Login or Register to read the full article

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

Replies

Please login or register to join the discussion.

avatar
02nd Feb 2007 11:12

no, it's Audit Journal
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.

Thanks (0)
avatar
22nd Jan 2007 11:32

what about the Purchase Ledger table?
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.

Thanks (0)
avatar
By Anonymous
22nd Jan 2007 20:14

Are we using the right table .......
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??

Thanks (0)
avatar
By Anonymous
18th Jan 2007 18:24

Complimentary Approach ....
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

Thanks (0)
avatar
By Anonymous
07th Feb 2007 10:01

Try this .....
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;

Thanks (0)
avatar
20th Apr 2007 17:54

Analysis ready tutorial
David,

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

Many thanks,

Stuart

Thanks (0)
avatar
29th Apr 2007 09:26

next article?
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.]

Thanks (0)
avatar
22nd Jun 2007 17:50

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

Thanks (0)
avatar
23rd Aug 2007 12:15

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

Thanks (0)
avatar
03rd Aug 2010 10:12

Sage Analysis File

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 

Thanks (0)