#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] SAGE 200 SQL Query

## aaron_burr

Hi, I am trying to pull data from Sage 200 via MS Query, the only piece of information I can't seem to pull is the latest balance of any given nominal ledger account! can anyone help with me with the possible SQL code or point me in the right direction.

Apologies for any incorrect terms - I am teaching myself SQL and MS Query and only 2 days into this first project of mine.

Thanks in advance
Aaron

----------


## Kyle123

You've picked a hard one to start with! The Sage 200 database takes some getting your head round. 

You should have a View called NLAccountPeriodBalanceVw that *I think* will get you the actual value for a given account number and Accounting Period

----------


## Kyle123

Actually, you might need this to give you a rolling total http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=191091

----------


## AB33

Off-topic. Lately, Sage has been bragging it has now changed it's back-end to SQL-Server as if it is a new kind of technology. Why writing some common reports in Sage so horrendous is beyond me? The company got way with some unbelievable crap software for so long. I have lost counting the number of new releases over the years.

----------


## Kyle123

I don't think it's recent. sage 200 has been sql server for years, I thing sage 50 was access based. The database isn't that bad, it's just not intuitive how the tables relate to each other, you need to know how it's supposed to work to get meaningful reports out of it. To be honest, I've never tried to use the report writer, I do all the reporting I need in SQL, the views are pretty handy for common stuff, there just aren't very many of them (in our implementation at any rate) and documentation seems somewhat lacking

----------


## AB33

Hi Kyle,
In the past,  some people were referring to sage's database as "Flat". I  know now the reason- Access. Is not that scandal- a top 100  listed company had used Access database for years?  I am speechless.
If you, as a top IT wizard, find Sage reporting as cumbersome, I do not know how the mere mortals are supposed to use them. You live not far the Sage's headquarter. Any thought of giving some of your advice to Sage developers? :Smilie:

----------


## Kyle123

Having had a read, I was wrong, they used their own propriety database for Sage 50 and SQL Server for Sage 200, they presumably did this to keep licencing costs down.

Hahha I wouldn't call myself a wizard by any stretch! - One of the reasons is that it's cumbersome is quite legitimate, a lot of the data is necessarily flat, as the saying goes "accountants don't use erasers", this means that loads of stuff needs de-normalising to capture it as it was at the time and not how it is now - there isn't really a nice way round that.

----------


## AB33

Hi Kyle,

I am really interested to find out how you are using SQL with Sage, but I am also mindful of stealing of someone's thread.
Do you write the SQL statements in excel or in Sage, that is, as if it is a SQL Server database. Could you please point me to any resource to read or refer to? Thanks

----------


## Kyle123

Our use of Sage is probably different to most peoples, our in house built ERP raises invoices and posts them to Sales Ledger, so most of our SL reporting comes from our ERP. Most of the reporting I do links sage data back to our ERP to look at things like Aged debt, debtor days for credit control and cash collection. I also frequently have to do some reporting to link our PO system with the purchase and nominal ledgers. I don't do any of this in Excel, the  typically I write reports in SSMS, and copy and paste them to Excel, or I data warehouse them/ I may occasionally deploy reports to SSRS if there's a recurring - especially for things like KPIs oh and there are a few refreshable worksheets using MS Query based on data warehouse views.

----------


## aaron_burr

Thanks Kyle, that is what I am after. I looked through the vast list so many times I must have overlooked that table multiple times.

I am not going to look into the rolling total as it's overkill for me, I can just SUMIF from the list of all period balances for each Cost Centre.

Thanks Again.

----------

