How to Create This Query ? help please

[powerbuilder]

How to Create This Query ? help please

Hi,

I have the following tables:

1) Accounts...........5 Fields
2) transactions.......4 Fields


the data in Accounts Table :
Account No. Account Name Father Account
-----------------------------------------------------------------
1.........................Assets‏
11.......................Current Assets.......1............Assets‏
1101....................Banks..................11..........Current Assets‏
1101001...............National Bank........1101.......Banks
1101002...............ABC Bank.............1101.......Banks
1101003...............NFR Bank.............1101.......Banks
1102....................Customer's............11..........Current Assets‏
1102001...............Customer no 1.......1102.......Customer's
1102002...............Customer no 2.......1102.......Customer's
1102003...............Customer no 3.......1102.......Customer's

-------------------------------

the data in transactions Table :

Trns. no Account No. Amount
---------------------------------------------
1...............1101002..........15000$
2...............1101003 .........16500$
2...............1102002..........10750$

****************************************************

Now ....My Question is how to Create Query to give me Like This


Account No. Account Name Balance
----------------------------------------
1.........................Assets...................42250$
11.......................Current Assets........42250$
1101....................Banks...................31500$
1101001...............National Bank.........0$
1101002...............ABC Bank..............15000$
1101003...............NFR Bank..............16500$
1102...................Customer's..............10750$
1102001..............Customer no 1.........0$
1102002..............Customer no 2.........10750
1102003..............Customer no 3.........0$


thank you

 

[Jon K]

You can do it with a series of two queries.

Run the second query in the attached database.
.

 

[ecniv]

Select tblAccounts.*, nz(tblTrans.amount,0) as TAmount
From tblAccounts left join tblTrans on tblAccounts.AccountNo=tblTrans.AccountNo

Looks like a stright retrieval (as above). If you need it summed it would change it to.. possibly the following:

Select tblAccounts.AccountNo, tblAccounts.AccountName, sum(nz(tblTrans.amount,0)) as TAmount
From tblAccounts left join tblTrans on tblAccounts.AccountNo=tblTrans.AccountNo
Group by tblAccounts.AccountNo, tblAccounts.AccountName

Vince

 

[DLB]

Now ....My Question is how to Create Query to give me Like This

Account No. Account Name Balance
----------------------------------------
1.........................Assets...................42250$
11.......................Current Assets........42250$
1101....................Banks...................31500$
1101001...............National Bank.........0$
1101002...............ABC Bank..............15000$
1101003...............NFR Bank..............16500$
1102...................Customer's..............10750$
1102001..............Customer no 1.........0$
1102002..............Customer no 2.........10750
1102003..............Customer no 3.........0$

Vince,

I don't think your queries can give powerbuilder the required Account Balances i.e.

Account No. 1		[b]42250[/b]
Account No. 11 		[b]42250[/b]
Account No. 1101	[b]31500[/b]
etc.

This kind of summing for "Parent Accounts" is used in many accounting systems.

DLB

 

[powerbuilder]

many thanks

thank you very much

thanks a lot