Monthly Income/Expense Reports

[Robin Chattopadhyay]

Hi-

I've had good luck with a recursive query. It works with my CoA because I
have no more than five levels. You would have to add another level of
recursion if you have more than five levels deep.

Call this query "accounttree":

SELECT
Nz([l4].[code],Nz([l3].[code],Nz([l2].[code],Nz([l1].[code],Nz([top].[code],"")))))
AS acct_code,
Nz([l4].[guid],Nz([l3].[guid],Nz([l2].[guid],Nz([l1].[guid],Nz([top].[guid],"")))))
AS acct_guid, top.code, top.name, L1.code, L1.name, L2.code, L2.name,
L3.code, L3.name, L4.code, L4.name, L3.hidden, L4.hidden
FROM ((((accounts AS root LEFT JOIN accounts AS [top] ON root.guid =
top.parent_guid) LEFT JOIN accounts AS L1 ON top.guid = L1.parent_guid) LEFT
JOIN accounts AS L2 ON L1.guid = L2.parent_guid) LEFT JOIN accounts AS L3 ON
L2.guid = L3.parent_guid) LEFT JOIN accounts AS L4 ON L3.guid =
L4.parent_guid
WHERE (((top.code) Not Like "9*") AND ((root.account_type)="ROOT") AND ((
root.name)="Root Account"))
ORDER BY top.code, L1.code, L2.code, L3.code, L4.code;