Creating a "Hierarchy"

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have also posted this question in "General
Discussions". I would not normally post in multiple
groups, as a matter of fact, this is my first time doing
it. I'm in kind of a bind and really need help with this
one.

I have a very large list of associates in Excel and I need
to create an organization chart out of it.

The fields in the Excel sheet include Employee Name and
Supervisor Name. There will be several cases where
someone listed in the Supervisor Name column is also
listed in the Employee Name column and obviously have a
different Supervisor.

I need to go down within the hierarchy as far as it can
go. I have no idea how many levels.

If anyone understands what I'm talking about and can help,
I would appreaciate any tips.

Thanks in advance.
Frank
 
Dear Frank:

Working with hierarchies is limited, but should be practical for the
instance you suggest.

Once you link or import the spreadsheet, the first thing I'd do is
write a query to show all the cases where there is a Supervisor Name
but where that Supervisor Name does not appear as an Employee Name.
The results of this will tell you a lot about how useful the existing
data is to create the kind of results you're after.

Another potential problem occurs when John Smith is the supervisor of
Mike Johnson, and Mike Johnson is the supervisor of John Smith.
Either there are two people with the same name, or the data does not
actually represent a hierarchy.

Next, it is useful to conceptualize the problem with levels. The
bottom up approach to this is as follows: Every employee who is not
anyone's supervisor is at level 1. Eliminating level 1 from the table
(in a temporary sense) repeat this process. Everyone who is not
anyone's supervisor (exlcuding those already identified as level 1) is
a level 2. Repeat till you know how many levels there are.

All this can be done with queries, assuming you add a column for the
level number.

The rest of what you need depends on where you're going with this.
The way I prefer to do it is to create a large text field to show the
complete hierarchy for each employee. If employee name is, say, 30
characters and there are 5 levels, this column will be 150 characters
long. Doing this technically violates normalization rules, in that it
is a derived column and it is compound. But we are going to use this
column in limited ways, and it gets too slow if you don't do this.

The purpose of this composite hierarchy column is to allow you to put
the list of employees in a neat "outline form" where each name is
indented according to his/her level and falls within an outline like
this:

President
President's Secretary
Vice President 1
Vice President 1's Secretary
Directory of Finance
Finance Assistant 1
Finance Assistant 2
Vice President 2
Directory of Accounting

and so on.

This is just one of the things you can do with what I propose.

See what you can digest from this, and tell me all you can about where
you want to go.

Warning: this is a moderately expert kind of project, requiring good
query skills, and probably VBA work with recordsets.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top