Need help designing organizational reports from an Access Database

[James]

I need to build an access database that consists of
individuals holding various level management jobs in
various companies. Then I need to be able to generate a
report that will list the companies alphabetically as well
as under each company, list the people that work for that
company starting with the CEO/President and working on
down to mail room guys. Is there a link to a sample
database that covers this type of organization?

In other words, I need to generate a report like the
following with indentation formatting designating the
organization level (level of supervision so to speak):

Company1

PersonA - Chief Executive Officer
PersonE - President of Finance
PersonJ - Treasurer
PersonF - Senior Accountant
PersonZ- Junior Accountant
PersonB - Payroll
PersonD - MailRoom Guy
PersonX - President of Marketing
PersonK - Marketing Manager
PersonG - Magazine Editor
PersonZ- Adds Salesman
PersonB - Graphic Designer


And so on...

Right now, I have one big table with:

-Key/ID, (interger)
-Person name, (PersonA, PersonB. Person Z)
-Company working for, (Co1, Co2, Co3, Co4, Co5)
-Level Code, (interger - I figured I'd make a field called
Level Code and give the CEO a "1", the vice president
a "2" and so forth on down the line to about "5" or "6"
levels deep)
-Position at that company, (CEO, President, Vice-
President, Manager, Mailroom, etc. by looking at this
field, I would determine what the previous field, the
Level Code number, would be)
-Comments (general comments)

I'm at the design phase right now and need to come up with
a way for users to be able to change a Person's position
at a company (and even change which company he works for)
and have all other people below him shift accordingly
(this to me seems like the worst case scenario, whereas if
a person just got a promotion, not as much data would have
to change). Take the above example: say PersonX's
position is eliminated and the company wants everyone
below him to move under PersonE's supervision. I guess the
user (me) would go to a form, find PersonX, change his
employer from Company1 to any other company, change his
Job Position and Level Code, and then somehow...
somewhere... make it known that the people under PersonX
shift over to be now under PersonE. I guess I could go in
and query all the people below PersonX and change them one
at a time, but I'm getting confused as to the most
efficient way to do this... any thoughts on a smart,
efficient way to do this? Even a link to a help page or
sample database that deals with building organization
level reports from a database would help. thanks so much--
James B.

 

[Chaim]

James,

I think I'd probably make some changes to the database design, if it is
truly only a single table. There seems to be a lot of occurrences of each of
the company names, for example; each employee at a company will have that
name in his record. A similar observation is relevant to the 'Level Code' and
'Position at that company'. Additionally, the way you describe it, you're
keeping redundant data (one field is derivable from another), which breaks
normalization. Source of many problems with DB applications.

Better way to do this might be to have a Company table consisting of:
companyID(long, primary key); companyName (text) and use the companyID in
place of the 'Company working for' field in your single table. The advantage
is that you only have to maintain company name in one place now, rather than
in multiple places. Can also keep address, etc. info if desired more easily.
Also with respect to the Level data: This is probably relatively static data
(doesn't change too often) and you might be better off with a Position Level
table consisting of levelCode(integer, PK) and positionName (text).

It looks like you keep track of the hierarchy (who works for who) by sorting
on the level within a company? I don't think that's going to work; suppose
the company has two or more VPs? Are they both at the same level? All of the
direct reports to each would show up as working for the other. Somehow, it
seems that you need to explicitly keep track of who the person works for.

I think a cleaner design might reveal to you how to get to the
report/functionality that you want/need.

Good luck!

 

[James]

Thanks, Chaim, for your thoughts. Over the last few days,
I've been thinking similar ideas, that is to be able to
build an organizational level of employees, each person
should have a field that identifies who he/she reports to.

Also, I've switched to three tables: person table, company
table, and position level table.

However now I'm still wondering how in the heck I'm going
to generate a report of the proper order of people in the
company. QUESTION: to generate this kind of report, do I
make a query that will list all the people from a specific
company in the proper order? Is there another way?

Example of Report: