Help converting working SQL into Access Query (1 Viewer)

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
The following SQL works on the backend. I cannot get the syntax right so it executes in Access. Any help to convert would be appreciated - thanks.





UPDATE tblDebtAccountNumber SET fldSOI = "1"
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))
);
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:39
Joined
Apr 27, 2015
Messages
6,286
Not sure if it’s a typo, but you have a space in your criteria “Negotiations” (you have “Negotiat ions”).

What kind of error, if any are you getting?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:39
Joined
May 21, 2018
Messages
8,463
Code:
UPDATE tbldebtaccountnumber 
SET    fldsoi = "1" 
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) ) );

May be easier to read.
http://www.dpriver.com/pp/sqlformat.htm
 

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
When I convert the working sql to this:



UPDATE tblDebtAccountNumber SET fldSOI = "1"
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]))
);





I get the following error:


"You tried to execute a query that does not include the specified expression 'SUM(T2.[Payment]) + T2.[SettlementFeePayments]' as part of an aggregate function."
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:39
Joined
Apr 27, 2015
Messages
6,286
(SELECT SUM(T2.[Payment]) + (T2.[SettlementFeePayments]) FROM tblPaymentsSub AS T2

Try
Code:
(SELECT SUM(T2.[Payment]) + (T2.[SettlementFeePayments])[COLOR="Red"])[/COLOR] FROM tblPay

My SQL skills are not what they should be but sometimes I guess right!
 

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
Code:
UPDATE tbldebtaccountnumber 
SET    fldsoi = "1" 
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) ) );
May be easier to read.
http://www.dpriver.com/pp/sqlformat.htm


Thanks for the input: I get the following error message:


"Invalid use of '.','!', or '()'. in query expression 'T1.tblclients_flddrcclientid=tbldebtaccountnumber.tblclients_flddrcclienti'.
 

isladogs

MVP / VIP
Local time
Today, 21:39
Joined
Jan 14, 2017
Messages
18,186
As well as the changes others have suggested, there is an unwanted space BEFORE the dot in this expression:
Code:
 'T1.tblclients_flddrcclientid=tbldebtaccountnumber .tblclients_flddrcclienti'.

and another one here after the dot:
Code:
WHERE T3.tblClients_fldDRCClientID=tblDebtAccountNumber. tblClients_fldDRCClientID

Are these just typos caused by forum glitches? They've been there since post #1
If you don't use code tags, the forum software inserts a space around every 50 characters
Suggest you go through & check carefully

Also, you may find this utility of mine useful: SQL to VBA & back again
 
Last edited:

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
As well as the changes others have suggested, there is an unwanted space BEFORE the dot in this expression:
Code:
 'T1.tblclients_flddrcclientid=tbldebtaccountnumber .tblclients_flddrcclienti'.
and another one here after the dot:
Code:
WHERE T3.tblClients_fldDRCClientID=tblDebtAccountNumber. tblClients_fldDRCClientID
Are these just typos caused by forum glitches? They've been there since post #1
If you don't use code tags, the forum software inserts a space around every 50 characters
Suggest you go through & check carefully

Also, you may find this utility of mine useful: SQL to VBA & back again


Hi. The typos are coming from Notepad
 

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
Try
Code:
 (SELECT SUM(T2.[Payment]) + (T2.[SettlementFeePayments])[COLOR=Red])[/COLOR] FROM tblPay
My SQL skills are not what they should be but sometimes I guess right!


But we are renaming tblPaymentSub as T2 why would that work?
 

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
Still getting the same error as in post#4 above
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:39
Joined
Apr 27, 2015
Messages
6,286
Is it possible for you to attach a copy of your database? The thought of recreating this query gives me a headache...
 

DKoehne

Registered User.
Local time
Today, 14:39
Joined
Apr 10, 2017
Messages
49
I have a working sql version for the backend. I will automate that. Thanks everyone for your feedback!
 

Users who are viewing this thread

Top Bottom