Update a field in a table depending on ...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi TWIMC,

I'd like to update a field for each record that relates to a parent record
with a calculation that depends on the summation of each relating record
within the table that relate to the same parent record. E.g. a parent record
has a value of 10 there are 4 records that relate to the parent record. Each
one of the four records has a wieght for the proportion it should receive
from the parent. So the 10 has to shared amoungst the 4 records by their
wieghted value.

So sub rec1 and rec2 have a weight of 2 sub rec3 and rec4 have a weight of
1, so 10 divided by 6 and multiplied by the weighted value would give the
correct proportion for each sub record.

So the question is, how do I go about it.

TIA

KM
 
First, get the total weight value:
TotWgtVal = DLookUp("[weight]","Child Table", "[ParentKey] = '" &
ParentKeyVal & "'"
(change then names as needed)

Next, get the weight factor
WeightFactor = ParentWgtVal / TotWeightVal

And then, use an update query or sql to update the child table field where
you carry the weight value
[weightval] = [weight] * WeightFactor
 
Back
Top