AnsweredAssumed Answered

Calculations across Related Tables

Question asked by keycoachjohn on May 12, 2009
Latest reply on May 12, 2009 by philmodjunk


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)