GL data tables

Hello all - I'm new to Sage 500 ERP and want to access the data tables using MS Access or similar product for ad hoc queries. I managed to find the tables on our server and link to them but cannot find the table containing general ledger balances - essentially the information necessary for viewing a trial balance (ending balances).  I have read through the table listing and schema with descriptions of the various tables but the best I can find is tglAcctHist which contains debit and credit activity - but not balances.  Does anyone have any suggestions?

Thank you!

  • I don't believe there is such a table (I am not the GL person). Perhaps you can use vdvAccountHistory which does most of the work for you, giving you the beginning and ending balances (and many other things) for an account by period. This can let you know the balance for a particular period,, or the final balance if you use the last record. Using the view shields you from having to know the particulars (or you can look at the view if you really want to know). Views can used like tables in most cases. With this, you just have to restrict it to the time period that you want.

    Try the following query to see how it might be able to help you out.  IT assumes demo data is installed.  If not, adjust the WHERE clause as appropriate.

    SELECT
    FiscYearPer
    ,BegBal
    ,CurrPerAmount
    ,EndBal
    ,*
    FROM
    vdvAccountHistory
    WHERE
    GLAcctNo = '1010000000'
    AND CompanyID = 'SOA'

  • in reply to Ramon M.
    Thank you! The vdvAccountHistory table does have the information I need. I have been focusing on the tglXXXXX tables but the views contain exactly what I need. Thanks again!
  • in reply to Jkassner

    A note of caution on this:  We have found with the vdvAccountHistory table is only updated if there were GL transactions in that period.  Therefore, if a GL does not have transactions within a period, the balance for that GL is reported as $0 (or should I say, it simply doesn't report a balance for that GL) rather than carrying the balance forward from the prior month.  This is something to be careful of, and can be easily missed, depending on the size of your query and how you validate your query results. 

    We have looked for many years to find the GL beginning and ending balances and have been repeatedly advised by our Sage vendor that those fields do not exist.  The GL TB calculates the field within the Crystal Report.  It is not a field that can be queried accurately, due to the issue mentioned above.  You either need to do a customization to your Sage software, or create your query to calculate the balance.