Hello. The included query runs fine up to the Is Null statement. I am adding the T4 statement and getting a group by error. The trouble is with the Last Statement! Any help to straighten it out would be appreciated. Thanks.
UPDATE tblDebtAccountNumber SET fldSOI = "0"
WHERE ((tblDebtAccountNumber.fldRecordStatus)="Negotiations" Or (tblDebtAccountNumber.fldRecordStatus)="Legal") AND
(
((tblDebtAccountNumber.fldEnrolledDebt*0.5) + (tblDebtAccountNumber.fldEnrolledDebt*0.25)) >=
((SELECT SUM(T1.Deposit_Amount) FROM tblDeposits_SPA_Cleared AS T1 WHERE T1.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID) -
(SELECT SUM(T2.Payment) + (T2.SettlementFeePayments) FROM tblPaymentsSub AS T2
WHERE T2.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID AND T2.Paid IN('1','0')) -
(SELECT SUM(T3.fldDeposit_Amount) FROM tblNSF AS T3 WHERE T3.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID OR 'IsNull')) AND
(SELECT(T4.SettlementID) FROM tblSettlements AS T4 WHERE T4.SettlementID = MAX(SettlementID) AND T4.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID)
);
UPDATE tblDebtAccountNumber SET fldSOI = "0"
WHERE ((tblDebtAccountNumber.fldRecordStatus)="Negotiations" Or (tblDebtAccountNumber.fldRecordStatus)="Legal") AND
(
((tblDebtAccountNumber.fldEnrolledDebt*0.5) + (tblDebtAccountNumber.fldEnrolledDebt*0.25)) >=
((SELECT SUM(T1.Deposit_Amount) FROM tblDeposits_SPA_Cleared AS T1 WHERE T1.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID) -
(SELECT SUM(T2.Payment) + (T2.SettlementFeePayments) FROM tblPaymentsSub AS T2
WHERE T2.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID AND T2.Paid IN('1','0')) -
(SELECT SUM(T3.fldDeposit_Amount) FROM tblNSF AS T3 WHERE T3.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID OR 'IsNull')) AND
(SELECT(T4.SettlementID) FROM tblSettlements AS T4 WHERE T4.SettlementID = MAX(SettlementID) AND T4.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID)
);
Last edited: