[AccessD] Uncertainty Data

My database has two main tables (and zillions of others): tblParts stores
the data for every part and assembly, and tblHierarchy stores the structure
of assemblies. I'll give a simplified structure for each table, listing only
what's germane to the problem at hand:

tblParts

        PartID          long (PK)
        Weight          double (lbs)
        UncertCode      text

tblHierarchy

        HierID          long (PK)
        PartID          long (FK)
        NHA             long (FK)

NHA = Next Higher Assembly. If parts 2 and 3 are components of assembly 1,
then all three would be listed in tblParts, and tblHierarchy would list 2
and 3 as PartIDs, each having 1 as its NHA (as in the example below).

UncertCode lists the code that identifies the level of certainty we
(officially) have in the record's weight. For the purpose of this
discussion, we'll say there are A, E, and R (Actual, Estimate and Roll-up,
respectively). If tblParts shows an R, then the Weight will not be native to
that record, but will be a simple sum (roll-up) of its components' weights. 

What I need is to know what percentage of the total weight is represented by
each UncertCode. Since an "R" just shows data from components, I don't want
its data; OTOH, I do want its components' data (assuming they aren't
roll-ups themselves). 

Let see a simple example.

tblParts:

PartID  Weight  UncertCode
    1        10           R
    2          6                  R
    3          4                  A
    4          4                  E
    5          1                  E
    6          3                  A


tblHierarchy:

HierID  PartID  NHA
    1         2     1
    2         3     1
    3         4     2
    4         5     2
    5         5     2
    6         5     3
    7         6     3


Part 1 is the main assembly, and I'm looking for its data; I need the weight
represented by A and E codes. When traversing down the tree, R will just
mean "keep going", while A and E mean "stop here and record the data, then
go back up to the next limb on the tree".  Starting at part 1 (in
tblHierarchy) as the NHA, we see it has two parts, 2 and 3. Part 2 is an R
(in  tblParts), so we need to go on. We see that parts 4 and 5 are in 2;
they are both E, so we can stop there. Going back to part 1, we now take
part 3. Since it's an A, we can stop there (even if its components' weights
did not add up to its given weight, we would not care). So, in this case, we
have 

A:  Part 3 (4 lbs)
E:  Part 4 (4 lbs) and 2 of Part 5 (2 x 1 lbs)

Total weight is 10 lbs, so we find that A represents 4 lbs, or 40%, and E
represents 6 lbs or 60%. 

As I said, this is an easy example. I have 8 uncertainty codes, several
vehicles (corresponding to Part 1 in this example; in other words, several
starting points) about 15 levels and thousands of parts. What would be the
best way to traverse the tree, getting only the non-R data for a vehicle?

Your help would be very much appreciated.

Tom Ewald
Detroit Area