B
Bart
I have numerical data that can be categorized, among
other things, by a structure like:
GROUP Value
113100 $1,000
113120 $1,450
113340 $5,000
121000 $2,500
175000 $1,000
etc...
Eventually, all of the groups (from 110000 to 199990) add
back to their parents with the top-level parent being
100000. The parent child structure looks like:
Parent Child
113000 113100
113200
113300
113400
etc....
113100 113110
113120
113130
etc....
165000 165100
165200
165300
etc...
so anyway, I may receive inputs at any particular group
level. There are a possible total of 6 levels at which I
could receive the data. For example:
Level 1: 100000
Level 2: 150000
Level 3: 152000
Level 4: 152500
Level 5: 152510
Level 6: 152514
If someone gives me data at level 6 -- "152514" group, I
need the levels above that to reflect the total in all of
its levels below it.
The only way I know how to do it right now is that on the
structure table on which I have the groups, I have 6
columns for each of its Level1Parent, Level2Parent,
etc... and then run 7 queryies, summing the data by the
level at which the number was estimated, and then summing
each level of the parents. After completeing this, just
adding everything together in a table. However, with over
100,000 inputs it is quite taxing on the system. Is there
and easier way to do it? Any help would be greatly
appreciated.
Thanks
Bart
other things, by a structure like:
GROUP Value
113100 $1,000
113120 $1,450
113340 $5,000
121000 $2,500
175000 $1,000
etc...
Eventually, all of the groups (from 110000 to 199990) add
back to their parents with the top-level parent being
100000. The parent child structure looks like:
Parent Child
113000 113100
113200
113300
113400
etc....
113100 113110
113120
113130
etc....
165000 165100
165200
165300
etc...
so anyway, I may receive inputs at any particular group
level. There are a possible total of 6 levels at which I
could receive the data. For example:
Level 1: 100000
Level 2: 150000
Level 3: 152000
Level 4: 152500
Level 5: 152510
Level 6: 152514
If someone gives me data at level 6 -- "152514" group, I
need the levels above that to reflect the total in all of
its levels below it.
The only way I know how to do it right now is that on the
structure table on which I have the groups, I have 6
columns for each of its Level1Parent, Level2Parent,
etc... and then run 7 queryies, summing the data by the
level at which the number was estimated, and then summing
each level of the parents. After completeing this, just
adding everything together in a table. However, with over
100,000 inputs it is quite taxing on the system. Is there
and easier way to do it? Any help would be greatly
appreciated.
Thanks
Bart