Solved

I Need SQL Query Statment For Showing Balance Sheet

Posted on 2003-11-05
10
1,591 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

726 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