Hi,

Is it possible to do subqueries when connecting to Sage 50 via ODBC connection?

I would like to do something like: 

SELECT AUDIT_HEADER.INV_REF, AUDIT_HEADER.DETAILS, AUDIT_HEADER.DATE, AUDIT_HEADER.DUE_DATE, AUDIT_HEADER.NET_AMOUNT, USAGE.AMOUNT
FROM AUDIT_HEADER, AUDIT_SPLIT, (SELECT SPLIT_NUMBER, SUM(AUDIT_USAGE.AMOUNT) AS AMOUNT FROM AUDIT_USAGE GROUP BY SPLIT_NUMBER) AS USAGE
WHERE AUDIT_HEADER.HEADER_NUMBER = AUDIT_SPLIT.HEADER_NUMBER AND AUDIT_SPLIT.SPLIT_NUMBER = USAGE.SPLIT_NUMBER

But I get at "Table not found" error.

Alternatively, doing more than one join would be useful, but this doesn't seem to work either?

Thanks,

Simon