Sort-of a sorting problem

G

Guest

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
 
M

Marshall Barton

John D said:
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?
Click to expand...


I've always done this sort of thing by converting the user
entered data to a canonical form. If all of the values are
of the form 001.010.001.002.001 (i.e fixed length parts with
leading zeros), then the normal sorting/grouping operations
will work on these strings.

Actually, space characters would be better than zeros if you
were to do this at data entry time, Since space is not one
of the possible characters in the values, it would be easy
to remove them for display using the Replace funtion.

The values can be converted to the standardized form using a
custom function that you can put together using a loop and
the built in functions InStr and Mid.

I don't understand in what context you need to do the totals
calculations, so all I can say at this point is that the
standard aggegate functions will do this in each level's
group header/footer in a report.
 
R

Ronald Roberts

I have an application that uses a numbering system like yours.
I setup 5 integer fields. This makes it easy for sort and grouping
totals. Then use a format or concatenation to put the number back
together.

Ron
 
Top