I have two tables - a MasterTable and a ChildTable - related by a field "PO_Number" (set as Primary key in the MasterTable)
There is a column "PO_Status" in the MasterTable and its corresponding column "Status" in the ChildTable.

I need to refresh a column in the MasterTable automatically, as below:

For a particular "PO_Number":
  • If the "Status" for All of the records (in the ChildTable) is blank; then the "PO_Status" of the MasterTable should display "Not done".
  • If the "Status" for any of the record (in the ChildTable) has a value "Done"; then the "PO_Status" of the MasterTable should display "Partially done".
  • If the "Status" for All the records (in the ChildTable) has a value "Done"; then the "PO_Status" of the MasterTable should display "Completed".
Help from the Access programmers will be highly appreciated.