The field totalacc holds the parent account number.


>I don't see from your data which accounts have subaccounts or if account is main account or subbaccount and what subaccount's main account is.
>
>>I have 3 tables with the following structure:
>>
>>Accounts
>>---------
>>

>>Account
>>Description
>>TotalAcc     &&& Account where totals will be added
>>DetailAcc    &&& Logical value representing where
>>             &&& the account will allow details
>>...
>>

>>
>>Details
>>--------
>>

>>Account
>>Date
>>Debit
>>Credit
>>Fiscal       &&& Fiscal Year
>>Period       &&& Corresponding period
>>

>>
>>Balances
>>---------
>>

>>Account
>>Fiscal
>>Period
>>Balance
>>

>>
>>When I try to determine the balance for all the accounts I need to add the balance from the previous period of the fiscal year to the transactions added during the present period. So far so good, but when an account has sub accounts it doesn't add up the balance of those sub accounts, how can I do that?
>>
>>This is the query so far
>>
>>

>>Select A.Account,A.Description,
>>Isnull((Select Balance From Balances Where Account=A.Account And Period=12 and Fiscal='2003' ),0)
>>+Isnull((Select Sum(Debit-Credit) From CNTDET02 Where Account=A.Account And Period=1 And Fiscal='2004'),0)
>> As Balance, A.TotalAcc
>>From Accounts A Order By rTrim(lTrim(A.Account))
>>

>>
>>Thanks in advance,
>>
>>Enmanuel