calc field based on child table
I'm going to describe what I need, then explain what I think needs to happen architecturally.
I have a ASSET Table and a child GOAL Table. There are many Goals per one Asset. Each Goal has a status field, with many possible results.
I want to have a "Status" field for each Asset that displays a calculated result based on the various statuses of child Goals.
For example: If any of the child Goals are "In Progress", I want the parent status to read "In Progress." If any of the child Goals are "Pending AA" or "Pending BB" or "Pending CC," I want the parent status to read "Pending."
If I put the calc field in the parent table, it will only look at the first related record, correct? Therefore, I think I should create a 3rd table that will house the calc field.
I'm also looking to display the ASSETS table in list view if possible, and will need to generate reports based on Asset Status, so speed is an issue. What is the most sound way to approach this problem?
Please advise. Thanks in advance. :)