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