Derived Field

[Garret]

I'd like to insert a derived field into this situation

tblShipments (Parent table)
Shipment_No
Shipment_Size


tblShipment_Inspection (Child table)
Shipment_No (connected field)
Item_Dimension
(part of the item being inspected to see if it is "good")
Number_Out_Tolerance
(number of items in the shipment that are bad for this
dimension)


I'd like to create the field "Der_Percent_Good" that calculates the
percentage of good items in the shipment.
(1 - Number_Out_Tolerance/Shipment_Size)*100.


How would I do this since the tables are related by Shipment_No, but
the actual size of the shipment is in the parent table but the
derived
field would be in the child table.

Thanks!

 

[John W. Vinson]

I'd like to insert a derived field into this situation

tblShipments (Parent table)
Shipment_No
Shipment_Size


tblShipment_Inspection (Child table)
Shipment_No (connected field)
Item_Dimension
(part of the item being inspected to see if it is "good")
Number_Out_Tolerance
(number of items in the shipment that are bad for this
dimension)


I'd like to create the field "Der_Percent_Good" that calculates the
percentage of good items in the shipment.
(1 - Number_Out_Tolerance/Shipment_Size)*100.


How would I do this since the tables are related by Shipment_No, but
the actual size of the shipment is in the parent table but the
derived
field would be in the child table.

Thanks!

It wasn't necessary to post your question four times - maybe the newsreader
you're using wasn't showing that you had already done so.

The derived field should certainly NOT be stored in either the parent or the
child table; it should instead by calculated dynamically as needed, using a
Totals query. It's not clear to me how the calculation should be done - if you
have multiple inspection records, do you want the SUM of the number out of
tolerance divided by the shipment size? What if the same items were out of
tolerance on several dimensions? They'd be double-counted if you sum. If 6% of
the shipment is out of tolerance on part A, and 6% is out of tolerance on part
B, and 11% is out of tolerance on part C, what is the desired result - 23%?
That may well be inaccurate, if in fact 6% of the shipment is messed up on all
three dimensions; it might just be 11%. It does not appear that your tables
contain the information needed to resolve this ambiguity!

John W. Vinson [MVP]