You should "split" record in tAccountsJV into two, one for each account by joining it twice. After that you apply adjustment appropriately. Code below only demonstrates that idea. It also may not work for backend you're using.
lcAcctID = STR(tiAcctID)
...
SELECT t.iID, t.iNo, t.dDt,
s.bDrAmt - uf.bDr + ut.bCr,
s.bCrAmt - uf.bCr + ut.bDr
FROM tAccountsJV t
JOIN sAccountsJV s ON t.iID = s.iPID
AND s.iAcctID = ?lcAcctID
JOIN uAdjustments uf
ON uf.iPID = s.iPID
JOIN uAdjustments ut
ON ut.iSID = s.iPID
You would store info in two records (one FROM and anothe TO account) in tAccountsJV if it was normalized.
>Hi all
>
>The following SQLExec() will get me the parent-child details
>
SQLExec(THISFORM.nConnHnd, ;
> [SELECT t.iID, t.iNo, ] ;
> + [t.dDt, s.bDrAmt, s.bCrAmt FROM tAccountsJV t, sAccountsJV s ] ;
> + [WHERE t.iID = s.iPID AND s.iAcctID = ] + STR(tiAcctID), ;
> [curtAccountsJV])
>brow norm
>
>returning something line this
>151 1 01/04/2004 100.00 0.00
>165 2 05/04/2004 0.00 60.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
>
>
>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
>
>
>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
>
>
>sAccountsJV the Child table
>iID Integer PK
>iPID Integer FK
>iAcctID Integer Account code
>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
>
>
>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
--sb--
