A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

parents summing children



 
 
Thread Tools Display Modes
  #1  
Old August 11th, 2004, 04:23 PM
Bart
external usenet poster
 
Posts: n/a
Default 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  
Old August 11th, 2004, 11:15 PM
Adrian Jansen
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 04:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.