Excel zone
Feature
Tutorial: Sage and MSQuery #6 - Add customer and supplier names. By David Carter
When you export data out of Sage, it includes account codes of customers and suppliers, but not their names. This may not matter for your own use, but any report for a manager has to include full names and descriptions. In his final tutorial of the series, David Carter adds customer and supplier names to the analysis-ready file.1. Why does Sage omit customer and supplier names?
Right now in the MASTER database we have an ACCTNO field in column G which holds the account code, whether it’s from the Sales or Purchase Ledger or the Nominal Ledger. We’d like to create a column ACCTNAME next to it with the customer/supplier name. However, this is not possible - if you remember in tutorial #2, we physically made a join to another Sage data table and it all seemed to be working OK, but then we started losing records.
The reason is that Sage holds customer and supplier names in different places. The former are stored in the Sales_Ledger table, the latter in the Purchase_Ledger table. For us to pull them into the analysis ready file, they have to be held in single table.
This can’t be done in Sage, so it’s time for Plan B – do it in Excel.
In the tutorial that follows I’ve used Query to import the customer codes and names into a worksheet, then I’ve imported the supplier codes and names into the same columns underneath. This gives us our single lookup table.
It’s a bit of a bodge job really, but it worked OK on the Sage Demo company.
2. Create the AcctName lookup table in Analysisready.xls
Start up Analysisready.xls.
Open or create a new worksheet. Name it AcctNamelookup.
We’ll do the Customer file first. In cell B1 type CUSTOMERS
Click onto cell B2 (we are leaving column A blank for the moment).
From main menu (Excel 2003): Data – Import External Data – New Database Query
(Excel 2000 or Excel 97): Data – Get External Data – Create Database Query
The Choose Data Source box appears. If it doesn’t, you need first to set up Query and Sage ODBC. Go through article one of this series.
Sage Demo Company is in the list of data sources. Highlight it and OK.
In the Sage ODBC Connect screen, UserID = MANAGER, Password – leave blank. OK.
Query Wizard–Choose Columns appears, with ACCRUAL, AUDIT HEADER etc.
3. Insert the Account Codes and Names
In the list of tables at the left, scroll down until you get to SALES_LEDGER. Click the + sign next to it.
Select the top two items – Account_Ref and Name.
Now just hit hitting Next and finally select: Return Data to Microsoft Office Excel.
A list of customer account codes and names appears.
We’ll now enter the supplier codes and names underneath, so go to the bottom of the list.
Leave a few blank lines, then type SUPPLIER into column B.
Click onto the cell below.
Now again, choose Data-Import External Data.
This time go to the PURCHASE_LEDGER table. Open it and select AccountRef and Name.
A list of supplier names appears.
4. Add “S” or “P” in front of the codes
There is a danger that you are using the same account code for a supplier and a customer. For example, in the Sage Demo company there is customer STU001 Edward Stewart and a supplier STU001 Studio Designs.
So we will have to distinguish customer codes from supplier codes by sticking an S or a P in front, as follows.
Click onto cell A3 opposite the first customer code. Type in =”S”&B3
You should now see the account code, but with an S in front.
Copy this formula down all the customer codes.
Now go the list of supplier codes. In column A, do the same thing, but this time put a P in front.
Right click anywhere for menu, select Data Range Properties.
At the bottom, tick on Fill down formulas adjacent to data.
Also, above it, select the second option - Insert Entire Rows for new data. Then click OK.
(This will automatically add any new customer or supplier records from Sage.)
The lookup table is now ready. Click back onto MASTER worksheet.
5. Add “S” or “P” in MASTER
To make the lookup work, we have to add S and P to the account codes here as well.
Check where the TYPE (SI, PI etc) and the ACCTNO columns are (columns B and G in my data).
Now scroll to the far right of the worksheet. The first blank column is column R.
In R1 type the column heading TYP2; in S1 type ACCTNO2
In R2, the TYP2 column, enter =LEFT(B2,1) This means, “show the first digit of the value in column B” (the TYP column). You should see an S or a P
Copy this formula down to the bottom row.
In S2, the ACCTNO2 column, enter = R2&G2 Then copy down to the bottom row.
In S2 we now have the Account Codes with P or S in front.
6. Use VLOOKUP to pull AcctName into MASTER
Now click on the top of column H “Details”. Insert a blank column.
In H1 type the heading AcctName.
Click onto H2.
From the menu, select: Insert–Function (or simply click the fx icon).
[Excel 2003] The Insert Function box appears. In the big “Select a Function” box, find VLOOKUP. If it is not there, you can find it in the category: Lookup and Reference.
[Excel 2000] The Paste Function box appears. Check to see that VLOOKUP is in the right hand list. If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP.
Highlight VLOOKUP and click OK. The Vlookup box appears, starting with Lookup_Value.
Lookup_Value: Highlight column ACCTNO2 (far right) by clicking on the T at the top.
Moving lines appear around the “ACCTNO2” column and T:T appears in the Lookup_Value field.
Table_Array: Click the mouse onto the Table-Array field.
Click onto the acctnamelookup worksheet.
Click on the A to highlight column A, then drag to highlight columns B to C.
Click back into MASTER. Click onto the Col_Index¬Num field
Col_ Index_Num Type: 3
Range_Lookup Type: false OK
You should see the Customer/Supplier name appear in H2.
7. Copy Down to the other rows
Click onto cell C2.
Now drag the mouse to the RH bottom of the cell until it turns into a black cross
Double click to Fill Down to the bottom of the sheet.
Wait a few seconds (a message saying the percentage completed may appear at bottom left)
The column should now be filled with Account Names. (Some say #N/A, but they are Journals and Bank payments where there is no customer or supplier.)
8. Check that the worksheet is automatically refreshing
You just need to make sure that the MASTER sheet is refreshing.
From the menu select: Data Range Properties. At the bottom, make sure that:
Fill Down Formulas in Columns Adjacent to Data is selected
Enter new rows (the second option) is selected
If you like, you can now try entering a new transaction into Sage, then running Refresh to see what happens.
Well that’s it. To get around the defects in Sage we’ve added three lookup tables in Excel, one of which is itself a query.
You might feel that this whole workbook is getting a bit top-heavy with all these queries derived from queries, lookup tables, formulas, etc. I’m inclined to agree. But I have been trying this data import for a year or so now with my own customers. For the first few months I was expecting them to ring me any day with the news that the whole thing had collapsed.
But so far nothing has gone wrong. The auto-refresh mechanisms that Microsoft has set up in Excel do seem to be pretty robust. It does all seem to work.
END OF SESSION. Close down and Save Analysisready.xls.
Previous tutorials in the Sage & MSQuery series
Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
AccountingWEB.co.uk 19-Sep-2007
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 4928
There are some basic issues here
a. why on earth would one want to use the same PrimaryKey for both Customers & Suppliers; in fact why does Sage permit it? It un-necessarily complicates any extraction which would otherwise be a simple LEFT JOIN
Furthermore it completely fouls up merging customers/suppliers by the software house to allow both transactions types (sales/purchases) from the same entity
b. M$ Access does not have the concept of CASE statements as per other 'proper' SQL databases - instead it adopts the dreadful nested IIF approach used by Excel
Nevertheless using the IIF statement it is possible to extract all the information required in a single SQL statement - using access try the following
SELECT AUDIT_JOURNAL.TRAN_NUMBER, AUDIT_JOURNAL.TYPE, AUDIT_JOURNAL.DATE, AUDIT_JOURNAL.ACCOUNT_REF, PURCHASE_LEDGER.ACCOUNT_REF AS Supp, SALES_LEDGER.ACCOUNT_REF AS Cust, AUDIT_JOURNAL.NOMINAL_CODE, NOMINAL_LEDGER.NAME,
IIf(AUDIT_JOURNAL.TYPE='SI', SALES_LEDGER.NAME,
IIF(AUDIT_JOURNAL.TYPE='PI', PURCHASE_LEDGER.NAME, 'N/A')) AS Expr1
FROM ((AUDIT_JOURNAL 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 ON AUDIT_JOURNAL.NOMINAL_CODE = NOMINAL_LEDGER.ACCOUNT_REF;
You will need to add extra IIf statements to account for all the Transaction Types
(i.e. AUDIT_JOURNAL.TYPE='PA' etc)
This is only an example which can be expanded to fit ones needs
PS. If using a 'proper' SQL statement it would read something like
SELECT ......
CASE AUDIT_JOURNAL.TYPE
WHEN 'SI' THEN SALES_LEDGER.NAME
WHEN 'PI' THEN PURCHASE_LEDGER.NAME
END
FROM .......
Allowing a single SQL statement to extract the results required
