Tutorial: Sage and MSQuery #4 - Add the missing pieces of data. By David Carter

When you bring data out of an accounts package into Excel, key items of data are usually missing. How do you include them? Simple: use Excel's Vlookup function to pull in the extra data from a lookup table. David Carter continues his series on creating an analysis-ready file in Sage Line 50 and Instant.

1. The story so far
In Tutorial 1 we linked up Sage directly to Excel via ODBC.

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

Not sure about need for vLookup ....

Anonymous | | Permalink

Think we might be getting too complex with the vLookup table

On the assumption that the Sage table COMPANY contains START_MONTH and FINANCIAL_YEAR fields; we therefore know the Financial Year start - so if we have month=3 and year=2006 then build the date as 200603

The transaction dates for each entry are available in the format 28-June-2006. After removing the day element from the date reformat it to 200606

Surely by subtracting the financial year start date from the transaction date we can derive the company financial month - example (200606 - 200603) = 3; however as financial months are 1 based and not 0 based add 1 to this figure - giving (3 + 1) = 4. So 28-June-2006 is the 4th month in the company financial year assuming a company financial year start of March 2006

Any transactions with a calculation of less than 1 are before the year start and those greater than 12 are after the year end (assuming 12 periods)

No doubt someone will correct the logic if flawed but this is essentially the jist of it.

Access with Financial Year month ...

Anonymous | | Permalink

Try this - a single query ?

SELECT AUDIT_JOURNAL.TRAN_NUMBER, AUDIT_JOURNAL.DATE, AUDIT_JOURNAL.ACCOUNT_REF, NOMINAL_LEDGER_1.NAME AS NominalName, AUDIT_JOURNAL.NOMINAL_CODE, NOMINAL_LEDGER.NAME, 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 (((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;

Just copy and paste as query into Access - replace the date in bold/italics with your own Sage company FY start date

Access with Financial Year month & P/L capability ...

JC | | Permalink

Another Single query ? - does p/l lookup against CATEGORIES table

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));

Just copy and paste as query into Access - replace the date in bold/italics with your own Sage company FY start date

Sage_Lookup_Table_Periods.xls

johnpaulhenning | | Permalink

Where can I get this from? Sage_Lookup_Table_Periods.xls

I click and it opens up a blank page?

SQL in MS Query

richard67 | | Permalink

Rather an old thread, but wandered if anyone had made the SQL statements above work in MS Query rather than Access?