?
Solved

I Need SQL Query Statment For Showing Balance Sheet

Posted on 2003-11-05
10
Medium Priority
?
1,630 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

771 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