SageForum for UK versions of Sage 50- Help and support.

Well, in the end it didn't work completely via SQL as the Sage ODBC driver doesn't seem to support DISTINCT and the GROUP BY seems flaky.

So I basically run this query (there is a lot of 'stuff' from various tables):


SELECT
AUDIT_HEADER.TRAN_NUMBER,
AUDIT_USAGE.DATE
and a lot more stuff
FROM ...

Yay! The join (all table) at the beginning of the SQL statement does work. Odd that joining sequentially (in my previous example) doesn't.

Anyway, that is what I need.

Thanks a lot - excellent forum service!

Just SQL. JOIN, INNER JOIN, LEFT JOIN, LEFT OUTER JOIN. Nothing works.

The Sage ODBC driver is pretty awful (as is the general db schema - I guess no one at Sage HQ is big on normalisation) and very restrictive - which is why Sage are not keen to support it, even through the developer program ...

Ah yes. Thanks a lot John (and Bruce) that makes perfect sense.

I ran some queries and I can now see how the allocation\reconciliation model works.

However, I tried joining all 3 tables (AUDIT_HEADER, AUDIT_SPLIT, AUDIT_USAGE) to get all the info I want.


SELECT
stuff
FROM AUDIT_SPLIT ASP
JOIN ...

Good Morning,

I am using Sage 50 on 2010 (don't think what version I use makes much odds as the data model seems almost identical between versions) and need to export the all the item data (open or closed recently, for sales and purchase ledger).

It would appear the AUDIT_HEADER table has all the ...