[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

I Need SQL Query Statment For Showing Balance Sheet

Posted on 2003-11-05
10
Medium Priority
?
1,678 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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

650 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