Summarize Related Records in Calculation Field
Here's the setup: I have a table full of equipment records and another table full of room records. The equipment records are each assigned to a location which ties them to a particular room record.
I'd like to have a field in my room record table that calculates the total cost of all the equipment assigned to that room. This summary needs to update whenever equipment is reassigned or cost values change. I'm not sure if this can be done purely using a calculation field? I don't know how to "look up" multiple records from a related table and then summarize them using a single calculation.
I believe the proper table relationships are already established, essentially a one to many relationship based on the Room ID, so that one room record can have many related equipment records. Thanks for any input!