Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-29-2018, 02:45 PM   #1
DKoehne
Newly Registered User
 
Join Date: Apr 2017
Posts: 37
Thanks: 4
Thanked 0 Times in 0 Posts
DKoehne is on a distinguished road
Cool Group by issue with T4 statement

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)="Negotiati ons" 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 by DKoehne; 12-29-2018 at 04:22 PM.
DKoehne is offline   Reply With Quote
Old 12-29-2018, 03:05 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,297
Thanks: 0
Thanked 536 Times in 532 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group by issue with T4 statement

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
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 12-30-2018, 01:42 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,500
Thanks: 68
Thanked 2,725 Times in 2,610 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Group by issue with T4 statement

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 12-30-2018, 07:29 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,394
Thanks: 89
Thanked 1,650 Times in 1,532 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Group by issue with T4 statement

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, "") <>""
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-31-2018, 09:29 AM   #5
DKoehne
Newly Registered User
 
Join Date: Apr 2017
Posts: 37
Thanks: 4
Thanked 0 Times in 0 Posts
DKoehne is on a distinguished road
Re: Group by issue with T4 statement

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?
DKoehne is offline   Reply With Quote
Old 12-31-2018, 11:00 AM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,297
Thanks: 0
Thanked 536 Times in 532 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group by issue with T4 statement

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Tags
group by , query , update queries

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Option Group Issue fenhow Forms 1 12-09-2015 10:55 AM
GROUP BY HAVING Count statement fboehlandt Queries 14 08-04-2010 07:00 AM
Iif statement in group footer Rik_StHelens Reports 4 11-17-2009 07:13 AM
Update statement cannot use group by? lok1234 Queries 1 03-16-2009 07:32 PM
Group statement not working Mhypertext Queries 3 12-15-2008 01:39 PM




All times are GMT -8. The time now is 08:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World