I Need SQL Query Statment For Showing Balance Sheet

I have a MS Access Table AccountsDetail having following fields

AccountNo         AccName          Group        Parent        Type              Debit Amount

1                      Assets              Asset                           General
101                  CurrentAssets    Asset              1           General          
10101              Cash                 Asset           101           Detail                10000
2                     Liabilities           Liability                        General
201                 Payable             Liability            2           General            
20101             Farhan              Liability         201           Detail                -10000

I Need a SQL Query Statment, that could show a Balance sheet where only "General" Type Accounts must appear, carrying the Total Debit amount of thier child accounts which can be Further General or Detail Accounts.
Thanx in Advance.

LVL 1
fsaimsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mikal613Commented:
If the AccountNo is Stored as a text then you can do it with:

Select AccountNo,AccName,Group,Parent,Type,DebitAmount FROM AccountsDetail  WHERE Type = 'General' or Type = 'Detail' Order By AccountNo


This can ONLY BE DONE IF AccountNo is stored as TEXT

Text Sort VS Numeric Sort

fsaimsAuthor Commented:
AccountNo is stored as text, but actual problem is this that I want to update the Parent "General Type" Account's Debit Amount field with total debit amount of its child accounts and not show detail accounts in Balance Sheet. We can say "General" Type Accounts are actually the controlling accounts.
Mikal613Commented:
Select AccountNo,AccName,Group,Parent,Type,DebitAmount FROM AccountsDetail  WHERE Type = 'General' Order By AccountNo

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

fsaimsAuthor Commented:
Mika your query will just show the Accounts having Type "General", My Question is still that how can update the debit amount of Parent General Accounts with the total debit amount of Child accounts whether they or General or Detail Type
Mikal613Commented:
you will need another statement to update you cant use one for that but you would have to do a sum
fsaimsAuthor Commented:
I think I need a Update query in some kind of loop, can anybody help me in this sense, I am using ADO in VB.
kamlesh_agrawalCommented:
hi fsaims..

I think u want to show the records of "general" type..and the sum of debit amounts of the child accounts in the same result...

so try this...

select gen.*,(select sum(debitamounts) from accountsdetails where parent = gen.accountno) childsum from accountsdetails gen where gen.type = 'General'

You will get the sum of debits(child accounts) in childsum field...

Let me know if you need more help

rgds
Kamlesh A.

kamlesh_agrawalCommented:
i think the query would be like this as u r using MS-ACCESS

select gen.*,(select sum(debitamounts) from accountsdetails where parent = gen.accountno) from accountsdetails gen where gen.type = 'General'

bye
Kamlesh A.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fsaimsAuthor Commented:
Thanx Kamlesh for replying, here is only one Table named AccountsDetails, and I put some sample records in my actual question. I want to update the debitamount of Accounts having Type"General"  with the sum of debitamount of its Child Accounts. Child Accounts can be another General or can be Detail Type Accounts. Suppose
"Assets" is a General Type Account, its number is "1", It has not parent. "Current Assets" is a General Type Account, its number is 101, its Parent Account is "Assets". "Cash" is a Detail Type Account, its number is 10101, and its parent is "Current Assets". "Bank" is Detail Type Account, its number is 10102, and its parent is "Current Assets". "Cash" Account have debitamount of $1000 and "Bank" Account have debitamount of $2000. Now I want to update "Current Assets" debit amount with sum of "Cash" and "Bank" debitamount, and "Assets" debitamount with "Current Assets" debitamount.
Thanx
fsaimsAuthor Commented:
Thanx Kamlesh A. your mentioned Query is working perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.