# 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...

Tags

### Not sure about need for vLookup ....

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

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

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