Update Master Record Based on Detail Records
Bottom-up Query Examples - Detail Record Criteria Selects
In this example let us assume we have two tables in a
master/detail relationship. The main table contains Tasks which much be
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
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
("Update Tasks set Task_Complete=True " & _
" Where Tasks.Task_ID in " & _
" (Select Task_ID from Action_Items
where Task_ID=" & Me.Task_ID & _
Max(Action_Items.Action_Completed)=-1 " & _
" Group By Task_ID)")
("Update Tasks set Task_Complete=False " & _
" where Task_ID=" & Me.Task_ID)
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