I owuld like the following query to return the EndingBalance as part of the query. The EndingBalance should be returned as part of the embedded Select statement.
This part of the Select returns the correct information. SELECT mn_no, sb_no, dp_no, trx_amt, trx_dt,
But when I embed the second SELECT statement, the second SELECT statement is not returning the information I want. I must be doing something wrong as the Select Sum(T.Trx_Amt)... statement, as it is returning all 0's. It is as if the embedded SELECT statement is returning the value for all the records in the table rather than the individual totals for MN_No, SB_No and Dp_No combinations. This is a yearly General Ledger table and the sum of all the transactions must equal 0.
SELECT mn_no, sb_no, dp_no, trx_amt, trx_dt,
FROM GLTrxFil_SQL T
WHERE T.MN_No = MN_No AND T.SB_no = SB_No AND T.DP_No = DP_No
) AS EndingBalance
WHERE (trx_src = 'BBF')
ORDER BY mn_no, sb_no, dp_no, trx_dt
How do I fix the link between the records to return the sum of all the records with the same MN_No and SB_No and DP_No values?