J
John D
Hi there,
In a fix here with sorting. A series of tasks for a project i'm working on
are grouped using outline heirarcharchal format (i.i 1.1, 1.1.1, 1.2.2.,
1.2.3.2.1, etc.). Now, there are 5 levels of these heirarchies. The lowest
level (x.x.x.x.x) designates a particular ask. Each level above, is a
summary of the levels below that one. So, for instance, 1.1.2.1 would be a
summary of 1.1.2.1.1, 1.1.2.1.2, 1.1.2.1.3, etc.
What I need to do is the following:
1. Sort these into a list with proper outline format:
1.
1.1
1.1.2.1
1.1.2.1.1
1.1.2.1.2
1.2
1.2.1.2
....
2. I need to find a way to sum each lower level into a successive level (ie
summarixe all 1.1.2.1.x iinto 1.1.2.1) up to the top level.
Problems:
- Each of the lower level tasks is an aggregate sum already involving
several tables, subcalculation for each record, and summation of that sum
(per lowest level). So direct summ will not work (cannot do aggregates
within others).
- The hierarchy: using a text string would not sort when a level number is
2-digit. (ie 1.10 and 1.2 will sort to 1.10 first then 1.2 (not 1.2 then
1.10)). Separate fields could be used for each heirarchy level
(hn1.hn2.hn3.hn4.hn5), but this will be extremely tedious.
- Also, the heirarchy numbers are not hard-coded, they are to be entered by
the user.
Any advice here? This one's got me pulling out hair.. almost.
- How does one sum when essentially im performing several nested iteretions?
Is creating several Action Queries that create new tables, then requery the
answer?
- How can one sort by numbers properly using a str variable? Like operator
might work, but can someone please give help with this?
Thanks in advace
JD
In a fix here with sorting. A series of tasks for a project i'm working on
are grouped using outline heirarcharchal format (i.i 1.1, 1.1.1, 1.2.2.,
1.2.3.2.1, etc.). Now, there are 5 levels of these heirarchies. The lowest
level (x.x.x.x.x) designates a particular ask. Each level above, is a
summary of the levels below that one. So, for instance, 1.1.2.1 would be a
summary of 1.1.2.1.1, 1.1.2.1.2, 1.1.2.1.3, etc.
What I need to do is the following:
1. Sort these into a list with proper outline format:
1.
1.1
1.1.2.1
1.1.2.1.1
1.1.2.1.2
1.2
1.2.1.2
....
2. I need to find a way to sum each lower level into a successive level (ie
summarixe all 1.1.2.1.x iinto 1.1.2.1) up to the top level.
Problems:
- Each of the lower level tasks is an aggregate sum already involving
several tables, subcalculation for each record, and summation of that sum
(per lowest level). So direct summ will not work (cannot do aggregates
within others).
- The hierarchy: using a text string would not sort when a level number is
2-digit. (ie 1.10 and 1.2 will sort to 1.10 first then 1.2 (not 1.2 then
1.10)). Separate fields could be used for each heirarchy level
(hn1.hn2.hn3.hn4.hn5), but this will be extremely tedious.
- Also, the heirarchy numbers are not hard-coded, they are to be entered by
the user.
Any advice here? This one's got me pulling out hair.. almost.
- How does one sum when essentially im performing several nested iteretions?
Is creating several Action Queries that create new tables, then requery the
answer?
- How can one sort by numbers properly using a str variable? Like operator
might work, but can someone please give help with this?
Thanks in advace
JD