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

Kashflow logo
5

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. Then in Tutorial 3 we brought over Sage transactions from the Audit Journal into Excel, and from this data created a pivot table report.

To test the live link between Sage and Excel, we entered a new invoice into Sage. Then we went into Excel and pressed the Refresh button. The pivot table report was updated automatically.

This is the key benefit of going into Excel...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk 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.

Share this content

Replies

Please login or register to join the discussion.

avatar
By Anonymous
23rd Aug 2007 16:36

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.

Thanks (0)
avatar
By Anonymous
23rd Aug 2007 19:40

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

Thanks (0)
avatar
By JC
23rd Aug 2007 19:44

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

Thanks (0)
avatar
31st Jul 2009 12:03

Sage_Lookup_Table_Periods.xls
Where can I get this from? Sage_Lookup_Table_Periods.xls

I click and it opens up a blank page?

Thanks (0)
avatar
23rd Apr 2014 15:00

SQL in MS Query

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

Thanks (0)