Group by issue with T4 statement

[DKoehne]

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)
);

 

[June7]

Unless IsNull is text in field, this should not be in apostrophes and is two words. If you are testing for null field:

WHERE T3.tblClients_fldDRCClientID = tblDebtAccountNumber.tblClients_fldDRCClientID OR tblDebtAccountNumber.tblClients_fldDRCClientID Is Null

 

[arnelgp]

if the expression you are building is too complex, better to build a UDF and call it from the query.

 

[The_Doc_Man]

June is spot-on with her comments. There are two possible syntaxes of interest.

WHERE table.field IS NULL ... used specifically to test for null fields. The inverse is WHERE NOT (table.field IS NULL)

WHERE NZ(table.field, "") = "" ... used to "keep on going" if you encounter a null or an empty field. (Could use that with 0 for number fields, too). The inverse would be be WHERE NZ(table.field, "") <>""

 

[DKoehne]

Here's the catch: Table T3 (NSF) may not have a record for the client (sometimes yes sometimes no). NSF's only happen when they happen. If there IS a record even with a zero value and I use Or Not Exists - it works. When there is NO NSF record for that client and I use Or Not Exists it runs successfully according to mysql but does not update the records. Thoughts?

 

[June7]

Why save aggregate data? This is usually unnecessary and can be dangerous (summary data can get 'out of sync' with raw data). If you can calculate for the UPDATE then can calculate whenever needed.