[AccessD] Accounting Tree

> In my MS Access Accounting application, I have a table named tblAccounts 
> which contains: AccountNo (Primary key), AccountName,MainAcNo, Main (boolean 
> value). The accounts table is arranged like this: MainAccountNos->sub 
> accountnos. For example:
> AccountNo	AccountName		MainAcNo	Main
> 100000		Assets			0		yes
> 110000		Fixed assets		100000		yes
> 111000		Buildings		110000		yes
> 111001		Building No.1		111000		no
> ........... and so on..
> Here all the three accounts to the top are main accounts and the fourth 
> account is a sub account. The sub accounts are the only accounts that are 
> taken in transactions. I could generate the total debits and total credits 
> for each accounts through a query based on the transaction table. But the 
> problem is: How can I get the totals for the main accounts which are not 
> directly related to the transaction table in accordance to its position in 
> this accounting tree hierarchy.
> My transactions tables:
> (1) tblAcDataEntryMain: VoucherNo (Primary), Date.
> (2) tblAcDataEntryDetail: VoucherNo, AccountNo, Description, Debit, Credit.
> Please help me. Thanking you,
> pvkutty at msn.com
> 
> 

Apart from the debatable use of the Account Number as the Primary Key and whether 
or not you need the MainAcNo field  :-)

To Group 110000,120000,130000 etc use:
Select distinct 
Int(AccountNo/ 10000) * 10000
sum(Credit),
sum(Debit)
from
tblAcDataEntryDetail
Group by Int(AccoutnNo/10000) * 10000
order by Int(AccoutnNo/10000) * 10000

For 100000,200000,300000 etc use
Int(AccountNo/100000) * 100000

and so on.


-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.