Tutorial: Sage and MSQuery #5 - Add nominal categories. By David Carter

Following on from his previous tutorial, David Carter now introduces two more new fields into his Sage analysis ready file – the nominal categories. Having brought them in, we can now produce our Profit and Loss report.

We need to add two more items - the nominal categories - to the Analysisready.xls file we created in tutorial #3.

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

Extract from CATEGORIES with SQL ...

Anonymous | | Permalink

Is the Sage_Lookup_Nominal_Categories.xls necessary?

Believe one can extract Nominal code from the Sage CATEGORIES table using SQL and the BETWEEN command - which greatly simplifies the extraction

Look at this WHERE clause using BETWEEN

WHERE ......
((AUDIT_JOURNAL.NOMINAL_CODE) Between [CATEGORY].[LOW] And [CATEGORY].[HIGH])

Full SQL:

SELECT CATEGORY.CHART, CATEGORY.CATEGORY, CATEGORY.NAME, CATEGORY.LOW, CATEGORY.HIGH, AUDIT_JOURNAL.NOMINAL_CODE, AUDIT_JOURNAL.AMOUNT, AUDIT_JOURNAL.DELETED_FLAG, Year([DATE]) & Format(Month([DATE]),"00") AS YearMth, '200301' AS FyStart, ([YearMth]-[FyStart])+1 AS FinMth,
Format([DATE],"mmm") AS FinMthText
FROM CATEGORY, AUDIT_JOURNAL
WHERE (((AUDIT_JOURNAL.NOMINAL_CODE)>='4000' And
(AUDIT_JOURNAL.NOMINAL_CODE) Between [CATEGORY].[LOW] And
[CATEGORY].[HIGH]) AND ((AUDIT_JOURNAL.DELETED_FLAG)=1));

(also provides financial month calc from previous tutorial - substitute the date in bold/italics with your own Sage company FY start date)

You can add fields from the AUDIT_JOURNAL table as required or use this as the basis for a Cross-Tab query (Excel pivot equivalent)

This SQL works in Access and there is no reason why it should not work in MSQuery as well - after all its only a SQL query

The advantage with using SQL and BETWEEN is that the command is dynamic and will work under any circumstances even if the Chart of Accounts is changed by someone else without telling you. Whereas using an xls lookup means that one has to construct a specific unique lookup for each company which is open to Excel errors if the COA has changed