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

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

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

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;

David,

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

Many thanks,

Stuart

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

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