Group by issue with T4 statement (1 Viewer)

DKoehne

Registered User.
Local time
Yesterday, 18:44
Joined
Apr 10, 2017
Messages
49
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)
);
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 17:44
Joined
Mar 9, 2014
Messages
3,377
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

error reading drive A:
Local time
Today, 10:44
Joined
May 7, 2009
Messages
11,171
if the expression you are building is too complex, better to build a UDF and call it from the query.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:44
Joined
Feb 28, 2001
Messages
18,747
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

Registered User.
Local time
Yesterday, 18:44
Joined
Apr 10, 2017
Messages
49
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

AWF VIP
Local time
Yesterday, 17:44
Joined
Mar 9, 2014
Messages
3,377
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.
 

Users who are viewing this thread

Top Bottom