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