Hi all

The following SQL will get me the parent-child details

SELECT t.iID, t.iNo, t.dDt, s.bDrAmt, s.bCrAmt
FROM tAccountsJV t, sAccountsJV s
WHERE t.iID = s.iPID
AND s.iAcctID = 485 ;

returning something like this
151 1 01/04/2004 100.00 0.00
165 2 05/04/2004 0.00 60.00
178 3 10/04/2004 250.00 0.00


but the result I want is the following
151 1 01/04/2004 40.00 0.00
165 2 05/04/2004 0.00 0.00
178 3 10/04/2004 250.00 0.00

151 has 100.00 Dr - 60.00 Cr (from 165) thus 40.00 Dr
165 has Nil as all of it's amount (60.00 Cr.) has been used against 151
178 shows the original amount as is, as no adjustments have been performed


Is this possible?

Please advise.

I have the following tables (simplfied)

tAccountsJV the Parent table
iID Integer PK
iNo Integer Document Sr. No.
dDt Date Document Date

eg.
151 1 01/04/2004
165 2 05/04/2004
178 3 10/04/2004


sAccountsJV the Child table
iID Integer PK
iPID Integer FK
iAcctID Integer Account code our main concern is no. 555
bDrAmt Double Debit Amt.
bCrAmt Double Credit Amt.

eg.
1145 151 555 100.00 0.00
1146 151 8 0.00 100.00
1155 165 45 100.00 0.00
1156 165 555 0.00 60.00
1165 178 555 250.00 0.00
1166 178 7 0.00 250.00


uAdjustments table I called this the GrandMaster table for want of a
precise word
iId Integer PK
iPID Integer FK
iSID Integer Reference field
iAcctId Integer Account code
bDr Double Debit Amt. Adjusted
bCr Double Credit Amt. Adjusted

eg.
1234 165 151 555 0.00 60.00

The meaning of this above is tAccountsJV.iID = 165 has updated this record
in relation of
another tAccountsJV.iID = 151 for Account code = 555 Crediting = 60.00

There is no record for tAccountsJv.iID = 178 as there are no adjustments
done yet.





Regards
Bhavbhuti
___________________________________________
Softwares for Indian Businesses at:
http://ahmedabad.sancharnet.in/vso_ad1/
namitbn@...
___________________________________________


----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.806 / Virus Database: 548 - Release Date: 05/12/2004


[Non-text portions of this message have been removed]