Solved

I Need SQL Query Statment For Showing Balance Sheet

Posted on 2003-11-05
10
1,467 Views
Last Modified: 2006-11-17
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.

0
Comment
Question by:fsaims
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688399
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

0
 
LVL 1

Author Comment

by:fsaims
ID: 9688734
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.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688780
Select AccountNo,AccName,Group,Parent,Type,DebitAmount FROM AccountsDetail  WHERE Type = 'General' Order By AccountNo

0
 
LVL 1

Author Comment

by:fsaims
ID: 9688937
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
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688974
you will need another statement to update you cant use one for that but you would have to do a sum
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:fsaims
ID: 9689083
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.
0
 
LVL 2

Expert Comment

by:kamlesh_agrawal
ID: 9689110
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.

0
 
LVL 2

Accepted Solution

by:
kamlesh_agrawal earned 250 total points
ID: 9689220
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.
0
 
LVL 1

Author Comment

by:fsaims
ID: 9689330
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
0
 
LVL 1

Author Comment

by:fsaims
ID: 9707168
Thanx Kamlesh A. your mentioned Query is working perfectly.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now