Loop and test Child records - update parent (1 Viewer)

DKoehne

Registered User.
Local time
Today, 10:51
Joined
Apr 10, 2017
Messages
49
I have been searching to no avail to find a solution to the problem below (please see attachment for more details). Any help is appreciated - air code, referrals to similar solutions, etc.

Note: Table A gets a single parent record created and the paid default is false (zero). Table B gets one or more child records and the paid default is false (zero). I need to loop through table B and select all the related foreign key child records (these will have the same settlementID on them as shown in table B). Next, I need to test the paid column for true. If ALL are paid (true) then use sql to update parent record paid column to true in Table A. If ALL are not true, (as in fk 2 above then do nothing and move to the next related set of foreign keys in table B (as in 19). The process has to repeat through the whole table B. The goal is to update Table A record when all child records in B have been paid to true.
 

Attachments

  • SettlementID_Loop.pdf
    36.7 KB · Views: 74
Last edited:

June7

AWF VIP
Local time
Today, 09:51
Joined
Mar 9, 2014
Messages
5,423
There is no need to save this calculated data to table. It can and should be calculated when needed.

SELECT TableA.SettlementID, [CountOfTransactionID]-Abs([SumOfPaid])=0 AS IsPaid
FROM (SELECT TableB.SettlementID_FK, Count(TableB.TransactionID) AS CountOfTransactionID, Sum(TableB.Paid) AS SumOfPaid
FROM TableB
GROUP BY TableB.SettlementID_FK) AS Query1 RIGHT JOIN TableA ON Query1.SettlementID_FK = TableA.SettlementID;

Otherwise, use VBA code looping through Recordset to save value to table. Example code uses the above query named SettlementPayments.
Code:
Sub UpdateSettlementPaid()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM SettlementPayments WHERE IsPaid")
While Not rs.EOF
    CurrentDb.Execute "UPDATE TableA SET Paid = " & rs!IsPaid & " WHERE SettlementID = " & rs!SettlementID
    rs.MoveNext
Wend
End Sub
Query that uses an aggregate query cannot be used by Access in an UPDATE query object, hence the VBA procedure. An aggregate query can be used to INSERT records. So an alternative to above VBA is an INSERT query to append records to a temp table (table is permanent but records are temporary). Then the temp table can be used in an UPDATE query after which records are deleted from temp table until next time. These steps can be performed manually or, again, automated with VBA.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:51
Joined
May 7, 2009
Messages
19,169
there is no need to use Recordset.
a simple Update Query will suffice.
Code:
UPDATE TableA 
SET TableA.Paid = -1
WHERE 
TableA.Paid=0 And 
(((Select Count("*") from TableB As B Where B.SettlementID=TableA.[SettlementID])=(Select Count("*") from TableB As B Where B.SettlementID=TableA.[SettlementID] And B.Paid=True)));
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
26,999
Do this as a transaction. See this link for setting it up.

https://msdn.microsoft.com/en-us/library/office/ff835985.aspx

Code:
DBEngine(0).BeginTrans

UPDATE TableA SET PAID = TRUE ;

UPDATE TableA INNER JOIN TableB ON TableA.SettlementID = TableB.SettlementID SET TableA.PAID  = TableA.PAID AND TableB.PAID ;

DBEngine(0).CommitTrans dbForceOSFlush

No need for code. Doing it as a transaction stops others from seeing Table A's PAID field as TRUE from the first update until the second update resolves the issue for those cases that are not fully paid.

Further, since this will be VERY SIMPLE SQL it should be incredibly fast when compared against an explicit recordset loop.

If you weren't sure, that second UPDATE will make TableA.PAID = FALSE the first time it hits a case of TableB.PAID = FALSE.
 

June7

AWF VIP
Local time
Today, 09:51
Joined
Mar 9, 2014
Messages
5,423
@arnelgp, interesting the aggregation does not error (no GROUP BY clause).

@The_Doc_Man, what do you mean by 'no need for code' - did you mean no need for recordset? Doesn't code need CurrentDb.Execute?

However, both methods set every record except SettlementID 2 to True. I presume Settlements with no TableB records should not be indicated as paid in full.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
26,999
June7, you are right - I was referring to not needing recordset code. An explicit loop is to be avoided when SQL can do the job. I've learned that fact time and time again. Recordset code, because it is interpreted, is slower than molasses in a New Hampshire winter. SQL, because more of it is compiled, is far faster.

The original problem as stated can be answered with very limited code. It would be easy to make two pre-defined queries as shown and then use four lines of code: the begin, DoCmd.RunSQL first query, DoCmd.RunSQL second query, commit. (Or, of course, CurrentDB.Execute.)

The folks who say it isn't necessary to update at all are also correct, since there are indeed ways to do this with some kind of JOIN that implies a summation - but this two-query sequence solves the original question about how to do the update as simply as possible.

Which leads to another question. I'll pose the question but I am fairly sure I already know the answer is NO. We know that we can take an arithmetic sum via DSum as a domain aggregate or via SUM as an SQL aggregate. But is there a function that is the LOGICAL sum or product of a field? Like perhaps a DAnd or DOr type function? In logic terms, the Inclusive OR is analogous to mathematical addition and the AND is analogous to mathematical multiplication. I don't know of any such Boolean aggregates, but it would have made this problem to be solved with something as trivial as a simple one-line query.
 

June7

AWF VIP
Local time
Today, 09:51
Joined
Mar 9, 2014
Messages
5,423
I did attempt query solution before resorting to VBA. So, revisted the SQL only approach and came up with:
Code:
UPDATE TableA 
INNER JOIN (SELECT TableB.SettlementID_FK, TableB.Paid FROM TableB 
          WHERE TableB.SettlementID_FK NOT IN (SELECT TableB.SettlementID_FK FROM TableB 
          WHERE TableB.Paid=False)) AS Query1 
ON Query1.SettlementID_FK = TableA.SettlementID 
SET TableA.Paid = [Query1].[Paid];
Or don't UPDATE:
Code:
SELECT TableA.SettlementID, Query1.Paid FROM TableA LEFT JOIN
        (SELECT DISTINCT TableB.SettlementID_FK, TableB.Paid FROM TableB 
          WHERE TableB.SettlementID_FK NOT IN (SELECT TableB.SettlementID_FK FROM TableB 
          WHERE TableB.Paid=False)) AS Query1 
ON TableA.SettlementID = Query1.SettlementID_FK;
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
26,999
Like I said in the earlier post, there ARE ways to do the query without updating. They even make a lot of sense. There are some ugly methods that involve an even nastier "hack" - but it depends on improper knowledge. (I'll explain that after I show the hack.)

SELECT TableA.SettlementID, SUM(CLng( TableB.PAID AND 1 ) ) AS PSum, COUNT(TableB.TransactionID) AS TCnt, (PSum = TCnt) AS PaidInFull FROM TableA INNER JOIN TABLE B ON TableA.SettlementID = TableB.SettlementID GROUP BY TableA.SettlementID;

The "Improper knowledge" is that we shouldn't rely on knowing that TRUE = -1 and FALSE = 0 for a native Access Boolean variable. The above would work on a "pure" Access solution but you might have to check for a mixed SQL solution.

I was merely answering the explicit OP statement: "The goal is to update Table A record when all child records in B have been paid to true." Since he claimed to want to update, I showed how to most efficiently update.
 

Mark_

Longboard on the internet
Local time
Today, 10:51
Joined
Sep 12, 2017
Messages
2,111
@OP,

What do Table A and Table B track? From the description I am guessing batch transactions being posted? I am hoping that the above posts already gave you your answer to your immediate question, but this type of question often begets "Is this the right solution for your data"?
 

DKoehne

Registered User.
Local time
Today, 10:51
Joined
Apr 10, 2017
Messages
49
Thank you all for your replies. Great stuff -I am still working through them. I have set up the workspace solution to test and I am getting only the msgbox and no update at present. I have added the code to an on open event for a form that can be called via AutoExec along with all startup update queries: here's what I have which is not doing anything but running through the code and displaying the msgbox at finish (with no update happening): what would you suggest here?



Private Sub Form_Open(Cancel As Integer)

Dim wrk As DAO.Workspace
Dim dbMDR As DAO.Database
Set wrk = DBEngine(0)

Set dbMDR = CurrentDb

On Error GoTo trans_Err

'Begin the transaction

wrk.BeginTrans
DoCmd.Hourglass True

'run the statement
dbMDR.Execute "UPDATE tblSettlements SET PAID = TRUE UPDATE tblPaymentsSub INNER JOIN tblPaymentsSub ON tblSettlements.SettlementID = tblPaymentsSub.SettlementID SET tblSettlements.PAID = tblSettlements.PAID AND tblPaymentsSub.PAID;"


wrk.CommitTrans dbForceOSFlush


trans_Exit:
'Clean up
DoCmd.Hourglass False
wrk.Close
Set wrk = Nothing
Set dbMDR = Nothing
MsgBox ("Settlement Records Update Complete!")

Exit Sub

trans_Err:
'Roll back the transaction
wrk.Rollback
Resume trans_Exit


DBEngine(0).CommitTrans dbForceOSFlush
End Sub
 

June7

AWF VIP
Local time
Today, 09:51
Joined
Mar 9, 2014
Messages
5,423
That SQL statement doesn't make sense to me.

Did you try suggestion in post 7?

Please post lengthy code between CODE tags to retain indentation and readability.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:51
Joined
May 7, 2009
Messages
19,169
I think using join will not give you the correct result. See post #3, already tested this one.
The main idea of that query is compare how many are the transaction and how many if those transactions have been paid. If transaction count is same as the count of those paid then update the main table as paid.
 
Last edited:

DKoehne

Registered User.
Local time
Today, 10:51
Joined
Apr 10, 2017
Messages
49
Hello,



Below is the code being used at the moment and it does update tblsettlements.Paid field to 1. The problem is that it is doing so EVEN WHEN the child record fields from tblPaymentsSub are NOT all paid. It does not seem to be reading the three records correctly (where 1 true and two of them are false 0).



UPDATE tblSettlements SET tblSettlements.Paid = -1
WHERE tblSettlements.Paid=0 And
(((Select Count("*") from tblPaymentsSub As B Where B.SettlementID=tblSettlements.[SettlementID])=(Select Count("*") from tblSettlements As B Where B.SettlementID=tblSettlements.[SettlementID] And B.Paid=True)));




I HAVE ADDED TWO ATTACHMENTS FOR REVIEW
 

Attachments

  • tblSettlements-1.pdf
    28.8 KB · Views: 56
  • tblPaymentsSub.pdf
    29.9 KB · Views: 57

Mark_

Longboard on the internet
Local time
Today, 10:51
Joined
Sep 12, 2017
Messages
2,111
Is the COUNT( ) working properly? As I recall, you would not normally put * in quotes.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:51
Joined
May 7, 2009
Messages
19,169
Chm your query again. Both sub query should use tblPaymentsub. Here is the fix:

UPDATE tblSettlements SET tblSettlements.Paid = -1
WHERE tblSettlements.Paid=0 And
(((Select Count("*") from tblPaymentsSub As B Where B.SettlementID=tblSettlements.[SettlementID])=(Select Count("*") from tblPaymentsSub As B Where B.SettlementID=tblSettlements.[SettlementID] And B.Paid=True)));
 

June7

AWF VIP
Local time
Today, 09:51
Joined
Mar 9, 2014
Messages
5,423
My suggestions UPDATE only records in TableA to true that have records in TableB AND are all paid. Did you try either?
 

DKoehne

Registered User.
Local time
Today, 10:51
Joined
Apr 10, 2017
Messages
49
After a few column name tweaks here is a final version that is working correctly:



UPDATE tblSettlements SET tblSettlements.Paid = -1
WHERE tblSettlements.Paid=0 And
(((Select Count("*") from tblPaymentsSub As B Where B.tblSettlements_SettlementID=tblSettlements.[SettlementID])=(Select Count("*") from tblPaymentsSub As B Where B.tblSettlements_SettlementID=tblSettlements.[SettlementID] And B.Paid=True)));


thanks for your help. I am going to ask The_Doc_Man to weigh in on his solution to try to get that working as I am concerned since these two tables will be the two biggest tables we will have and he may be right from a transaction perspective...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:51
Joined
May 7, 2009
Messages
19,169
You can wrapped it inside the transaction if you want.

You use transactions in cases where you are concern that a power failure might occur during the update process.

Also when you are updating more than one table and a failure to update one can compromise the whole process.
 

DKoehne

Registered User.
Local time
Today, 10:51
Joined
Apr 10, 2017
Messages
49
Hey June7
I will be trying yours tonight to update only those that have all paid. Thanks for the inputs.
 

DKoehne

Registered User.
Local time
Today, 10:51
Joined
Apr 10, 2017
Messages
49
My suggestions UPDATE only records in TableA to true that have records in TableB AND are all paid. Did you try either?


I have run the update with the inner join and when the child records are 1 of 3 true, it wants to update three rows. It should only be updating one row. Here is the code:



UPDATE tblSettlements
INNER JOIN (SELECT tblPaymentsSub.tblSettlements_SettlementID, tblPaymentsSub.Paid FROM tblPaymentsSub
WHERE tblPaymentsSub.tblSettlements_SettlementID NOT IN (SELECT tblPaymentsSub.tblSettlements_SettlementID FROM tblPaymentsSub
WHERE tblPaymentsSub.Paid=False)) AS Query1
ON Query1.tblPaymentsSub.tblSettlements_SettlementID = tblSettlements.SettlementID
SET tblSettlements.Paid = [Query1].[Paid];
 

Users who are viewing this thread

Top Bottom