Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
Update Parent Record Based on Child Record Fields
Set Master Record Field From Detail Record Values
Home > Tutorials > Query Tutorials > Master/Detail Record Updates
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Access Tutorial


Update Master Record Based on Detail Records

Bottom-up Query Examples - Detail Record Criteria Selects Master Records

In this example let us assume we have two tables in a master/detail relationship.  The main table contains Tasks which much be completed.



You did not know that nearly 100 cable and satellite tv installers use our software to manage their device inventory. They never lose track of an expensive device.

We have expert enterprise-level database development capabilities for cloud/web-based databases through our partnership with Jay McCormick and his team.


The detail table contains one or more Action Items for each task.   The two tables are linked by Task_ID.  All Action Items must be completed before the Task is complete.  We want to mark the task as done when all the action items have been completed.  We have an AfterUpdate trigger associated with the Completed field in the Action Items form.  Here is the code to update the master table (Tasks) when all items are completed for the task.

Private Sub Action_Complete_AfterUpdate()

    If Me.Action_Complete=true then

        DoCmd.RunSQL ("Update Tasks set Task_Complete=True " & _
        " Where Tasks.Task_ID in " & _
        " (Select Task_ID from Action_Items where Task_ID=" & Me.Task_ID & _
        " Having Max(Action_Items.Action_Completed)=-1 " & _
        " Group By Task_ID)")

    Else

        DoCmd.RunSql ("Update Tasks set Task_Complete=False " & _
        " where Task_ID=" & Me.Task_ID)

    End If

End Sub

Ok... this is more of an SQL example than a visual basic example.  But this is a perfect example of creating a very simple solution to a potentially very complicated task.   The key to creating highly maintainable Access databases is to have a consultant who knows both SQL programming and visual basic programming.

Here is the trick to this query... If all the Action_Completed Yes/No fields are true (-1) then the Max of them will be -1.  If any are not true (0) then the Max will be 0.

You should have indexes on all table fields mentioned in the SQL statement.









A Blue Claw Software Design Template:

Meal Delivery Service Solution
 









A Blue Claw Database Design Article:

Cloud-Based Access Database
 









Blue Claw Database Design Downloadable Tutorial:
Programming Crosstab Query Example  








Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
 


Contact Information
Copyright 2000-2018 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials