ODBC Aged Creditors Query

[ODBCWanKenobi]

Hi All
I'm producing a workbook that sucks up all sorts of data from sage. TB, Projects, Nom ledger, etc all working well. I have written code to get the aged debtors & creditors listings but they includes future payments. Can anyone help with the code that would be required which would exclude future payments.

Aged Creditors Code as it stands...

Code: Select all

SELECT 
purchase_LEDGER.ACCOUNT_REF AS 'Account', 
purchase_LEDGER.NAME AS 'Supplier Name', 
Sum(AUDIT_HEADER.AGED_BALANCE) AS 'Balance', 
Sum(AUDIT_HEADER.AGED_FUTURE) AS 'Future', 
Sum(AUDIT_HEADER.AGED_30) AS '30', 
Sum(AUDIT_HEADER.AGED_60) AS '60', 
Sum(AUDIT_HEADER.AGED_90) AS '90', 
Sum(AUDIT_HEADER.AGED_OLDER) AS 'Older'
FROM AUDIT_HEADER AUDIT_HEADER, purchase_LEDGER purchase_LEDGER
WHERE 
purchase_LEDGER.ACCOUNT_REF = AUDIT_HEADER.ACCOUNT_REF AND ((AUDIT_HEADER.AGED_BALANCE<>0) AND (AUDIT_HEADER.DELETED_FLAG<>1) AND (AUDIT_HEADER.DATE<=?))
GROUP BY purchase_LEDGER.ACCOUNT_REF, purchase_LEDGER.NAME

Many thanks in advance
Sage 22.1.10.394 SP5
Excel 2013 32 bit
Win 10 64 bit

[ODBCWanKenobi]

Well, after speaking with sage support this morning it would appear that what I want to do is just not possible. My requirement would mean accessing the AUDIT_USAGE table which is not accessible via ODBC. Try to connect to it in MS Query and MS Query crashes out. I have tried this on 2 machines and got consistent results. THe guy I spoke to at sage said they themselves tried this in 2007 when Excel got 1m rows for the first time, and it just isnt possible. Bit of a disappointing answer for something as simple as an aged report but I wasn't left in any doubt.

[brucedenney]

The way to do it, is to run 2 reports.

In the first extract it "all".

In the second just get the "future" dated receipts/payments

take the "future" payments off the "all" value you have the result you want?