By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

N-number of subgroups

P: n/a
I have a group of data that is hierarchical. Level N reports to
level N-1 reports to.....Level 2 reports to level 1. I need to show
this data on my Access report in such a manner:

Level 1 -- Person A
Level 2 -- Person B
Level 3 -- Person D
Level 3 -- Person E
Level 3 -- Person F
Level 2 -- Person C
Level 3 -- Person G
Level 2 -- Person I
Level 1 -- Person H
Level 2 -- Person J
Level 3 -- Person M
Level 3 -- Person L

etc with no ability to limit the number of levels. In my tables, I
have a field that has the reporting-to id (i.e. direct manager's id)
AND a field for the level (1,2,3, etc). As you can see, there's no
rhyme or reason to the Person's name. I can't figure out for the
life of me how to report N-levels of grouping in reports. I've tried
subreports (only 2 levels) and I've tried VB and I've only succeeded
in confusing myself more. I know if I had a field for each group,
I'd have something to sort on, but b/c of the desire for N levels,
that's impossible. Is this just a matter of a complicated SQL
statement? If forced, I can limit to 8 levels deep.

Can someone help me out? Thanks so much!
Margaret
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
mj*********@hotmail.com (Margaret) wrote in message news:<21**************************@posting.google. com>...
I have a group of data that is hierarchical. Level N reports to
level N-1 reports to.....Level 2 reports to level 1. I need to show
this data on my Access report in such a manner:

Level 1 -- Person A
Level 2 -- Person B
Level 3 -- Person D
Level 3 -- Person E
Level 3 -- Person F
Level 2 -- Person C
Level 3 -- Person G
Level 2 -- Person I
Level 1 -- Person H
Level 2 -- Person J
Level 3 -- Person M
Level 3 -- Person L

etc with no ability to limit the number of levels. In my tables, I
have a field that has the reporting-to id (i.e. direct manager's id)
AND a field for the level (1,2,3, etc). As you can see, there's no
rhyme or reason to the Person's name. I can't figure out for the
life of me how to report N-levels of grouping in reports. I've tried
subreports (only 2 levels) and I've tried VB and I've only succeeded
in confusing myself more. I know if I had a field for each group,
I'd have something to sort on, but b/c of the desire for N levels,
that's impossible. Is this just a matter of a complicated SQL
statement? If forced, I can limit to 8 levels deep.

Can someone help me out? Thanks so much!
Margaret


Oh, this is an ugly. Haven't done it, but I've thought about this one
quite a bit. Only way I can think of doing it, and I may be
COMPLETELY wrong, is to do something like create a query that finds
everyone with no subordinates. Those are level N=0. Find the people
that manage those level 0 folks. those are level N+1. Keep looping
until you find records without a level#...

HTH,
Pieter
Nov 12 '05 #2

P: n/a
Create a function to pre-process you data into a temp table like you were
exploding a bill of material. The temp table would have a [Level] field and
a [Sort] field.

"Margaret" <mj*********@hotmail.com> wrote in message
news:21**************************@posting.google.c om...
I have a group of data that is hierarchical. Level N reports to
level N-1 reports to.....Level 2 reports to level 1. I need to show
this data on my Access report in such a manner:

Level 1 -- Person A
Level 2 -- Person B
Level 3 -- Person D
Level 3 -- Person E
Level 3 -- Person F
Level 2 -- Person C
Level 3 -- Person G
Level 2 -- Person I
Level 1 -- Person H
Level 2 -- Person J
Level 3 -- Person M
Level 3 -- Person L

etc with no ability to limit the number of levels. In my tables, I
have a field that has the reporting-to id (i.e. direct manager's id)
AND a field for the level (1,2,3, etc). As you can see, there's no
rhyme or reason to the Person's name. I can't figure out for the
life of me how to report N-levels of grouping in reports. I've tried
subreports (only 2 levels) and I've tried VB and I've only succeeded
in confusing myself more. I know if I had a field for each group,
I'd have something to sort on, but b/c of the desire for N levels,
that's impossible. Is this just a matter of a complicated SQL
statement? If forced, I can limit to 8 levels deep.

Can someone help me out? Thanks so much!
Margaret

Nov 12 '05 #3

P: n/a
Is it necessary that you group by N-level in your report? I'm not
sure how (or if you can indeed) define report groups in code at run
time, but you could create a report similar in structure to the one
you requested by using a tree transversal; that is, you would simply
be creating a report where you are listing all the employee in a
certain order, and then customizing the display based on N-level.
Read on if this is the case.

The idea is to create a temporary table has 2 fields: one referring to
the primary keys of your employees table, and one "DisplayOrder" that
is a long representing the order in which the employees are displayed
on the report. The primary key is used to uniquely identify the
employees so you can join them when creating a report, which should be
fairly trivial compared to the rest of this :P The "DisplayOrder" is
the hard part and requires a bit of work with what are called
pre-order tree-transversals. Let's consider your sample data:
Level 1 -- Person A (1)
Level 2 -- Person B (2)
Level 3 -- Person D (3)
Level 3 -- Person E (4)
Level 3 -- Person F (5)
Level 2 -- Person C (6)
Level 3 -- Person G (7)
Level 2 -- Person I (8)
Level 1 -- Person H (9)
Level 2 -- Person J (10)
Level 3 -- Person M (11)
Level 3 -- Person L (12)


What you want to do is associate each employee with a number. Person
A is displayed first, so he gets 1. Person B is displayed next, so
she gets 2. And so on. The resulting number appear in brackets
above.

The hard part here is how to get the numbers in the first place. For
this, you have to implement a recursive function and a private
DisplayOrder counter (let's call it COUNT) that will help you label
your employees appropriately.

The recursive function needs take a particular employee who has been
assigned a DisplayOrder, and retrieves all "child" employees who have
this guy as boss. If there are no child employees (this is called a
base case), then the function does nothing. However, if there are
child employees (this is called the recursive case), it takes the
first child employee, and assigns her the next available display order
(available via the COUNT variable). Further, the method needs to call
itself, but using the child employee in the parameter. Naturally, the
function will need to repeat this process for all the child employees.

If that was a bit confusing, let me try to clear this up with some
pseudocode. Let's say the recursive function is
numberEmployee(intEmpID) and we have a function that assigns an
employee the next available DisplayOrder called
assignDisplayOrder(intEmpID).

Sub numberEmployee(intEmpID as long)

find all child employees for current employee (with ID = intEmpID)
if no child employees found, then exit sub
while (loop through child employees)
assignDisplayOrder(currentChildEmployeeID)
numberEmployee(currentChildEmployeeID)
wend

End Sub

To make use of this method, you will need to use a nonrecursive
"wrapper method" for all the level-0 employees. Here's the
pseudocode:

Sub startNumberingEmployee()

find all level-0 employees (ones that don't have bosses)
while (loop through level-0 employees)
assignDisplayOrder(currentZerosEmployeeID)
numberEmployee(currentZerosEmployeeID)
wend

End Sub

If you trace this code, you should find that it will order all the
employees in a kind of "tree display order". Then, it should be
fairly easy to generate a report that displays all employees, sorting
on DisplayOrder, and subtly altering each displayed employee according
to his N-level.

You can even use this function to verify that all employees have the
appropriate N-levels. But note that this is a recursive function and
will run forever if you have any circular boss references in your DB.

I'm a bit of a novice with Access, so I hope this wasn't too
confusing. I'm sure someone will probably come up with a better
method before this post actually goes live.

Good luck!

Alan
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.