If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
parents summing children
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 |
#2
|
|||
|
|||
parents summing children
Get hold of Joe Celko's book on trees, or even his "SQL for Smarties" where
he discusses this type of problem. He has some neat solutions. -- Regards, Adrian Jansen J & K MicroSystems Microcomputer solutions for industrial control "Bart" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Summing Fields per record | alex | Running & Setting Up Queries | 5 | June 30th, 2004 02:12 PM |
How to add children and birthdays to contact | Jim Gizzi | Contacts | 0 | May 28th, 2004 04:53 AM |
Conditional Summing up of Cell values | Paul | Worksheet Functions | 0 | December 23rd, 2003 12:52 PM |
Conditional summing question | Steve Conway | Worksheet Functions | 7 | November 14th, 2003 11:50 PM |
Summing over multiple sheets | Gary Thomson | Worksheet Functions | 3 | October 23rd, 2003 01:33 PM |