[AccessD] Uncertainty Data

HI Thomas,

I've just come back and looked at this again.

One point struck me - would it be possible to change your table design to:

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

-- 
Stuart


On 12 Feb 2012 at 9:08, Thomas Ewald wrote:

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>