Calculations across Related Tables
I'm slooooowly starting to get the hang of related tables. One current challenge that I cannot find a solution in the current resources (books, forums etc) involves calculating data found across a one-to-many relationship. I don't think a summary report will work since the calculation is required in tables used further down in the whole DB.
For example, two tables represent the core of the ER, Table A and B:
Table A- "Log" with Fields: primary key, serial number (a value list with several serial numbers in inventory), and monthly utilization per serial number. Each record represents a new utilization input per serial number. This should be the "many" side of the relationship, but how to create a working aggregate utilization field based upon a serial number active in Table B?
Table B-"Product Status": Fields for Foreign key from Table A, Starting hours, Total Hours- a calculated field using "starting hours+ (aggregate utilization)