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

1

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.

Click the link to download a copy of the nominal categories lookup table.

1. Contents of Sage_Lookup_Nominal_Categories.xls

This lookup table contains the Nominal Categories in the Sage demonstration company, Stationery and Computer Mart.

There are two worksheets COA and PIVOT. Click onto PIVOT.

This is a pivot table. It shows the format of the P&L report we going to produce.

Now click onto the COA (chart of accounts) worksheet. The P&L...

Please Login or Register to read the full article

Replies

Please login or register to join the discussion.

Extract from CATEGORIES with SQL ...
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

Thanks (0)