Update fldSOI to 1 based on calcs - need some help (1 Viewer)

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
Looking for some feedback on how to structure this so it can be run as a daily update autoexec query:



Goal is to update fldSOI to "1" where the calculations result in TRUE
Zero is the default for fldSOI





UPDATE tblDebtAccountNumber SET fldSOI = "1" where (
SELECT tblDebtAccountNumber.tblClients_fldDRCClientID, tblDebtAccountNumber.fldRecordStatus, tblDebtAccountNumber.fldEnrolledDebt, [fldEnrolledDebt]*0.5 AS SettlementAmount, [fldEnrolledDebt]*0.25 AS SettlementFees, Nz(DSum("[Deposit_Amount]","[tblDeposits_SPA_Cleared]","[tblClients_fldDRCClientID]=" & [tblClients_fldDRCClientID]),0) AS DepositTTL, Nz(DSum("([Payment]) + ([SettlementFeePayments])","tblPaymentsSub","[tblClients_fldDRCClientID]=" & [tblClients_fldDRCClientID] & "And [Paid] = False"),0) AS CommttdPmts, Nz(DSum("([Payment]) + ([SettlementFeePayments])","tblPaymentsSub","[tblClients_fldDRCClientID]=" & [tblClients_fldDRCClientID] & "And [Paid] = True"),0) AS PaidPayments, Nz(DSum("[fldDeposit_Amount]","[tblNSF]","[tblClients_fldDRCClientID]=" & [tblClients_fldDRCClientID]),0) AS NSF, IIf(([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([ComttdPmts]-[PaidPayments]-[NSF]))="True","1","0") AS fldSOI, ([DepositTTL]-([ComttdPmts]-[PaidPayments]-[NSF])) AS AvailableBalance, [SettlementFees]+[SettlementAmount] AS AmountNeeded, [AvailableBalance]-[AmountNeeded] AS ForecastedAmount, tblDebtAccountNumber.fldNoteHoldDate
FROM tblDebtAccountNumber
WHERE (((tblDebtAccountNumber.fldRecordStatus)="Negotiations" Or (tblDebtAccountNumber.fldRecordStatus)="Legal") AND ((tblDebtAccountNumber.fldNoteHoldDate) Is Null)) OR (((tblDebtAccountNumber.fldNoteHoldDate)>=Date()));
 

Insane_ai

Not Really an A.I.
Local time
Yesterday, 20:50
Joined
Mar 20, 2009
Messages
264
Please help me understand what you need.
1. Fix the query
or
2. Schedule it to run daily.

If #2 then
1. Create a Macro that invokes the query, include action to close the database after.
2. Create a scheduled task like the following:
"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.exe" "C:\ MyDatabse.accdb" -x [macroname]

Replace "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.exe" with the path to your version of access

Replace "C:\ MyDatabse.accdb" with the path to your database. IF on the network, make sure it is trusted and you are using a mapped drive. I've had issues with UNC paths failing in the scheduled task.


Replace [macroname] with the actual name of the macro.
 

June7

AWF VIP
Local time
Yesterday, 16:50
Joined
Mar 9, 2014
Messages
3,377
How did you build that? It is really nonsensical with that SELECT.

Also fldSOI is a calculated field. Cannot UPDATE a calculated field. None of those calculated values are used in any way. Why are they in there?

The following does make sense if fldSOI is actually a native field in table:

UPDATE tblDebtAccountNumber SET fldSOI = "1"
WHERE (((tblDebtAccountNumber.fldRecordStatus)="Negotiations" Or (tblDebtAccountNumber.fldRecordStatus)="Legal") AND ((tblDebtAccountNumber.fldNoteHoldDate) Is Null)) OR (((tblDebtAccountNumber.fldNoteHoldDate)>=Date())) ;
 

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
Yeah, I know. This is convoluted one. Here is the background: I have a table of records and these are all child records. There can be multiple records PER FOREIGN KEY. I need to update fldSOI to true on EACH of these records based on the other criteria in the sql. I believe the statement represents what criteria is needed. Now, what I am searching for is a good way to do it syntax-wise.
 

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
Please help me understand what you need.
1. Fix the query
or
2. Schedule it to run daily.

If #2 then
1. Create a Macro that invokes the query, include action to close the database after.
2. Create a scheduled task like the following:
"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.exe" "C:\ MyDatabse.accdb" -x [macroname]

Replace "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.exe" with the path to your version of access

Replace "C:\ MyDatabse.accdb" with the path to your database. IF on the network, make sure it is trusted and you are using a mapped drive. I've had issues with UNC paths failing in the scheduled task.


Replace [macroname] with the actual name of the macro.


Thanks for the reply - it's number one (pls see my comment)
 

June7

AWF VIP
Local time
Yesterday, 16:50
Joined
Mar 9, 2014
Messages
3,377
Doesn't really change my comment. Except if fldSOI is a number type, remove quote marks.

The calculated fields in the SELECT are not part of criteria to determine which records to update. SELECT like that is not even valid syntax.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:50
Joined
Feb 28, 2001
Messages
18,747
Looking over that SQL nightmare, here is a specific comment. You have no WHERE clause in the UPDATE but used WHERE to specify that you had one. SQL should barf when it sees this. I know I almost did.

The enclosed SELECT statement has a WHERE clause but there is no linkage between the enclosed and enclosing SQL statements.

Here is my problem - and yours. I see in this question what appears to be a disorganized bunch of SQL that makes absolutely no sense. But hang on, I'm going to turn that into a positive statement. It tells me that you are still confused enough that you cannot state your question clearly IN ENGLISH and so have resorted to some SQL code. You are still working on the design here and need to go back to the drawing board. (Side question: Where did the inventor of the drawing board go when his first attempt failed?)

Anyway, you need to write out IN CLEAR ENGLISH (or if that isn't your preferred language, then use whatever suits you) the conditions under which your UPDATE should work to set certain fields in certain records to 1. Make a bullet-item list of the criteria that will qualify the record for selection for the update.

Now divide and conquer. You have a list of criteria in the SELECT sub-clause's WHERE statement. For the elements of that WHERE clause, include the fields that you need for the computation. YOU DON'T NEED all of the fields you listed because they don't contribute to the WHERE of the SELECT and there WAS no WHERE of the UPDATE so they don't contribute there, either. De-clutter. Reduce this to a minimum number of fields.

Further, take note that you have an EXPRESSION in the SELECT query that is listed "AS fldSOI" and this is an ABSOLUTE NO-NO. The field isn't bound to a table so you CANNOT update it. EVER. Think about what you REALLY wanted to do and explain that to us. But right now it appears that you don't really know what you want to do.
 

arnelgp

error reading drive A:
Local time
Today, 09:50
Joined
May 7, 2009
Messages
11,171
you are returning fields which are not needed on
updating fldSOI.
the query can be simplified:
Code:
UPDATE tblDebtAccountNumber 
 SET fldSOI = "1" 
FROM 
 tblDebtAccountNumber
WHERE 
 ((tblDebtAccountNumber.fldRecordStatus="Negotiations") Or (tblDebtAccountNumber.fldRecordStatus="Legal"))
 AND 
 (Nz(tblDebtAccountNumber.fldNoteHoldDate, Date()+1)>=Date());
 

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
OK. here we go ...the Doc Man wanted to see a checklist and



now that I have your attention:



This appears to be a four table join (all four tables share the foreign key field "tblClients_fldDRCClientID")

The purpose of the update query to tblDebtAccountNumber is to do some calculations, aggregate some values, do a compare, in order to determine which records have enough deposits to cover a transaction.

The end goal is to update the field "fldSOI" in tblDebtAccountNumber to "1" when the results equate to true - sounds simple right?

tables and fields needed:
tblDebtAccountNumber.tblClients_fldDRCClientID
tblDebtAccountNumber.fldEnrolledDebt
tblDeposits_SPA_Cleared.Deposit_Amount
tblPaymentsSub.Paid
tblPaymentsSub.SettlementFeePayments
tblNSF.fldDeposit_Amount


calculations:
fldEnrolledDebt x .5 AS SettlementAmount
fldEnrolledDebt x .25 AS SettlementFees

Domain Aggregates per foreign key used in:
DepositTTL - this is a sum of the deposit total by foreign key based on tblDeposits_SPA_Cleared.Deposit_Amount above
CommttdPmts - these are the sum of all committed payment amounts by foreign key from tblPaymentsSub.Payment (the committed here are paids that are false or = 0)
PaidPayments - these are the sum of all paid payment amounts by foreign key from tblPaymentsSub.Paid (Paid is = 1)
NSF - this is the sum total of all NSF amounts by foreign key based on tblNSF.fldDeposit_Amount

The following logic is how to determine if a record should be set to true:
([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([ComttdPmts]+[Paid]+[NSF])
If (SettlementFees + SettlementAmount) combined are less than the DepositTTL (all that came in)-(all that went out [ComttdPmts]+[Paid]+[NSF]) then it is true
The first part is "what's needed" - the second half is "how much we have."

I left the where statement off having to do with status and hold out dates. I can filter that in reporting or try arnglp's suggestion afterwards.


I am open to any simplifications that can be applied.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:50
Joined
Feb 28, 2001
Messages
18,747
The end goal is to update the field "fldSOI" in tblDebtAccountNumber to "1" when the results equate to true - sounds simple right?

Except that in the query, fldSOI is named using AS syntax applied to an expression. So you CANNOT update it. EVER. BUT it would be possible for you to write a sequence of queries that would have a given effect. You probably don't actually care if it is 1 or 0, you would probably be just as happy with True or False, particularly since it would then be trivial to turn one of those to the other.

To do what you are doing, you probably need to look at further, deeper division and rearrangement of your computations and queries. Although it might seem complex, it is very possible and in fact, rather easy to base a query off of another query. Queries don't need tables as inputs; they need recordsets. Recordsets come from tables and queries. So let's start with more specific ideas.

You have a bunch of DSum operations. You might get better, faster results if you wrote some isolated SELECT queries that used the SQL Aggregates to form sums where you group on your common key field value. You DON'T CARE that this select makes no decisions. This is part of a "divide and conquer" strategy, remember? In the aggregate query, use the AS syntax to name the fields as SumPayment or SumDeposit, things like that.

Now you can write other queries to bring together things related to the common key, perform your computations, and have them ready for your final comparison. You can use the query design grid to make local relationships between the components being brought into the final decision. Then you can write your expression such as

Code:
([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([ComttdPmts]+[Paid]+[NSF])

(or)

CBool(([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([ComttdPmts]+[Paid]+[NSF]))

If that second alternative statement is presented as a field in the layered SELECT query that joins together the other individual computations, it will be returned as True or False. There will be no need to attempt to update anything.

Layering of queries might at first seem contra-productive - but it is not. Further, since you suggest that your summations all depend on a similar key, you can write one (ugly) summation query for every case you WOULD have used DSum on that same key. Then you can use the returned values in the next layer of the query as though they were simple fields. (That's why you use AS syntax in that context.)

This obviates the need for any kind of UPDATE, which you couldn't do anyway in that context since you have no field in a table to HOLD that value in the first place.
 

June7

AWF VIP
Local time
Yesterday, 16:50
Joined
Mar 9, 2014
Messages
3,377
Not sure why this is a challege. Build SELECT query with expressions in filter critera. When you get the dataset you expect, click UPDATE button to switch query type, SET field to update. So if you want to save this value, make sure there is a field in table to receive it.

Yes, might need several queries to determine value, however, any sequence that includes aggregate (GROUP BY or DISTINCT) query will encounter 'not updatable' error.
 
Last edited:

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
you are returning fields which are not needed on
updating fldSOI.
the query can be simplified:
Code:
UPDATE tblDebtAccountNumber 
 SET fldSOI = "1" 
FROM 
 tblDebtAccountNumber
WHERE 
 ((tblDebtAccountNumber.fldRecordStatus="Negotiations") Or (tblDebtAccountNumber.fldRecordStatus="Legal"))
 AND 
 (Nz(tblDebtAccountNumber.fldNoteHoldDate, Date()+1)>=Date());


Hello! I added the specific criteria to better clarify - hope you can take a look. I'm looking for a good way to approach this problem. Thanks.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:50
Joined
Feb 28, 2001
Messages
18,747
Any way that updates fldSOI while that remains as a computed field is going to fail.

If you simply compute the value as part of a SELECT query, there is nothing to update.
 

arnelgp

error reading drive A:
Local time
Today, 09:50
Joined
May 7, 2009
Messages
11,171
try this:
Code:
UPDATE tblDebtAccountNumber SET fldSOI = "1" 
WHERE 
(
(([fldEnrolledDebt]*0.5) + ([fldEnrolledDebt]*0.25)) <= 
((SELECT SUM(T1.[Deposit_Amount]) FROM tblDeposits_SPA_Cleared AS T1 WHERE T1.[tblClients_fldDRCClientID]=tblDebtAccountNumber.[tblClients_fldDRCClientID]) - 
(SELECT SUM(NZ(T2.[PAYMENT],0) + NZ(T2.[SettlementFeePayments]) FROM tblPaymentsSub AS T2 WHERE T2.[tblClients_fldDRCClientID]=tblDebtAccountNumber.[tblClients_fldDRCClientID]) -
(SELECT Sum(NZ(T3.[fldDeposit_Amount],0)) FROM tblNSF AS T3 WHERE T3.[tblClients_fldDRCClientID]=tblDebtAccountNumber.[tblClients_fldDRCClientID]))
)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:50
Joined
Feb 28, 2001
Messages
18,747
ArnelGP - If you look at the big SELECT sub-query in the first post, you find this fragment:

Code:
IIf(([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([ComttdPmts]-[PaidPayments]-[NSF]))="True","1","0") [B]AS fldSOI[/B],

That field exists due to an AS clause renaming a computed field. It cannot be updated. Did you see any evidence that fldSOI actually exists in a table? Your syntax isn't wrong, of course, but the semantics might prevent this from being workable.
 

arnelgp

error reading drive A:
Local time
Today, 09:50
Joined
May 7, 2009
Messages
11,171
Yes i saw. If the op does not have the field then it will fail.
 

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
Currently, I have the following and it appears to be working ... but there is no update due to the AS on the fldSOI. I can run this in reporting and modify other design a bit.



I will try arnelgp suggestion after lunch (thanks for that).



The code running now that seems to be working on limited records is ...


SELECT tblDebtAccountNumber.tblClients_fldDRCClientID, tblDebtAccountNumber.fldRecordStatus, tblDebtAccountNumber.fldEnrolledDebt, [fldEnrolledDebt]*0.5 AS SettlementAmount, [fldEnrolledDebt]*0.25 AS SettlementFees, tblDebtAccountNumber.fldNoteHoldDate, qryUpdateSOIValues_4.NSF, qryUpdateSOIValues_2.CommttdPmts, qryUpdateSOIValues_2.PaidPayments, qryUpdateSOIValues_3.DepositTTL, IIf(([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([CommttdPmts]-[PaidPayments]-[NSF]))="True","Yes","No") AS fldSOI
FROM ((tblDebtAccountNumber INNER JOIN qryUpdateSOIValues_4 ON tblDebtAccountNumber.tblClients_fldDRCClientID = qryUpdateSOIValues_4.tblClients_fldDRCClientID) INNER JOIN qryUpdateSOIValues_2 ON tblDebtAccountNumber.tblClients_fldDRCClientID = qryUpdateSOIValues_2.tblClients_fldDRCClientID) INNER JOIN qryUpdateSOIValues_3 ON tblDebtAccountNumber.tblClients_fldDRCClientID = qryUpdateSOIValues_3.tblClients_fldDRCClientID
GROUP BY tblDebtAccountNumber.tblClients_fldDRCClientID, tblDebtAccountNumber.fldRecordStatus, tblDebtAccountNumber.fldEnrolledDebt, [fldEnrolledDebt]*0.5, [fldEnrolledDebt]*0.25, tblDebtAccountNumber.fldNoteHoldDate, qryUpdateSOIValues_4.NSF, qryUpdateSOIValues_2.CommttdPmts, qryUpdateSOIValues_2.PaidPayments, qryUpdateSOIValues_3.DepositTTL, IIf(([SettlementFees]+[SettlementAmount])<=([DepositTTL]-([CommttdPmts]-[PaidPayments]-[NSF]))="True","Yes","No")
HAVING (((tblDebtAccountNumber.fldNoteHoldDate) Is Null Or (tblDebtAccountNumber.fldNoteHoldDate)<Date()));
 

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
try this:
Code:
UPDATE tblDebtAccountNumber SET fldSOI = "1" 
WHERE 
(
(([fldEnrolledDebt]*0.5) + ([fldEnrolledDebt]*0.25)) <= 
((SELECT SUM(T1.[Deposit_Amount]) FROM tblDeposits_SPA_Cleared AS T1 WHERE T1.[tblClients_fldDRCClientID]=tblDebtAccountNumber.[tblClients_fldDRCClientID]) - 
(SELECT SUM(NZ(T2.[PAYMENT],0) + NZ(T2.[SettlementFeePayments]) FROM tblPaymentsSub AS T2 WHERE T2.[tblClients_fldDRCClientID]=tblDebtAccountNumber.[tblClients_fldDRCClientID]) -
(SELECT Sum(NZ(T3.[fldDeposit_Amount],0)) FROM tblNSF AS T3 WHERE T3.[tblClients_fldDRCClientID]=tblDebtAccountNumber.[tblClients_fldDRCClientID]))
)


Hello. I ran the sql and got an error - I'm on Access front end and mysql back end. I have attached the error for your review. I LOVE YOUR APPROACH - VERY SMART. Let's see if we can get it to work!
 

Attachments

  • arneglp_SQL_error.pdf
    11.6 KB · Views: 23
Last edited:

DKoehne

Registered User.
Local time
Yesterday, 17:50
Joined
Apr 10, 2017
Messages
49
Hello.

arnelgp's approach was choking a bit in access and mysql; after a few tweaks it is working fine in mysql but what is still missing is the following:
for this line -


(SELECT SUM(T2.Payment) + (T2.SettlementFeePayments) FROM tblPaymentsSub AS T2 WHERE T2.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID)
to have added to it a condition Where 'Paid' = 0 then SUM 'Payments' and 'SettlementFeePayments' and again for 'Paid' = 1 then SUM 'Payments' and 'SettlementFeePayments' for the associated foreign key records. Then it can be translated into access



I have attached a screenshot for validation...


Here is the working SQL:
UPDATE tblDebtAccountNumber SET fldSOI = "1"
WHERE
(
((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) -
(SELECT SUM(T3.fldDeposit_Amount) FROM tblNSF AS T3 WHERE T3.tblClients_fldDRCClientID=tblDebtAccountNumber.tblClients_fldDRCClientID))
);
 

Attachments

  • arneglp_SQL_add.pdf
    52.2 KB · Views: 19

Users who are viewing this thread

Top Bottom