Anonymous
Share this content

SAGE 200 SQL CODE EXAMPLES

SAGE 200 SQL CODE EXAMPLES IN EXCEL

Didn't find your answer?

HI

I have just started using Sage 200 with SQL in Excel. I now have the tables but getting stuck on the linking tables to each other to get a report out.

Is there any resources on the net or books with examples of 'how to produce a trial balance' for example.

Specifically I have been trying to produce a current outstanding transactions table from the purchase ledger into Excel that I can format into a pivot table.

I believe the tables I need are PLSupplierAccount, PLAllocationHeader, PLAlloicationTran, PLPosyedSupplierTran. That's about as far as I get, I think INNER JOIN comes into play but again I can't find information on these examples anywhere on the net. My Sage support company will produce the reports but will charge £'sss.

Thanks for any help you can offer.

Keith H.

Replies (6)

Please login or register to join the discussion.

avatar
By frankfx
05th May 2017 19:01

you may want to contact Glen Feechan.

He is a contributor to accountingweb

I have used his excel expertise in the past with accounts packages.

I recall that he has sage 200 experience

I have saved time producing reports and automated much of the routines and learnt much about excel as well.

Thanks (1)
Simon Hurst
By Simon Hurst
05th May 2017 19:07

Yes, Glen would certainly be worth talking to. More generally, the fourth part of the series on Power Query considered joins and join types:

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-part-4-vloo...

Thanks (1)
Routemaster image
By tom123
05th May 2017 21:13

Keith,

I use Sage 200 with ODBC links all the time.

If you want to PM me, I can email you a document that I have which describes most of the tables.

I can probably answer your query, as I produce TB, Aged Debtors, Stock valuation etc. = but this will be from my work machine, not home.

Rgds

Thanks (1)
avatar
By [email protected]
08th May 2017 17:10

Thanks all for your help, sure this knowledge will help no end.

Thanks (0)
avatar
By alan.falcondale
11th May 2017 10:25

Once you get started on the SQL it is relatively easy enough to follow...but there are pitfalls and "gotcha's"
Joins being one of them.
JOIN and LEFT JOIN being the ones to look at:
Join will join two tables on a given field where both fields are equal
LEFT JOIN will return all of the fields from the first table in the join to the matching field in the second table in the join. If a match is not found then the first table details will still be returned but with 'null' second table fields contents.

You may need to check out the field connections but they are generally self explanatory.
To find all of your tables and column names et cetera from your database, try this bit of SQL:
select * from INFORMATION_SCHEMA.COLUMNS
where
Table_Catalog = 'SAGE_200'

Thanks (1)
avatar
By pverco
11th May 2017 17:03

If you use Ms Query in Excel, you can use the graphical interface and it will construct the sql for you. It's similar to the Access interface and is a great learning tool

Thanks (1)
Share this content