MS ACCESS - Polulate Organizational Structure based on employee positionand location


I want to auto populate a company organizational structure, based on 2 fields called, position and location. For example

I have a President and Vice President, on the top of the organizational structure, this then branches off into 4 locations, each of which have managers, below the Vice President, and employees beneath them.

Right now, I have a Excel Doc that shows this, but maintaining this is a hassle. I want Access to hold this info, and auto generate the the organizational chart.

So,               Pres
   |                  |                   |                   |
Manager       Manager       Manager      Manager
|                        |                |                   |
Employees   Employees    Employees   Employees

Any suggestions?
Who is Participating?
harfangConnect With a Mentor Commented:
Your current structure doesn't look like a normal hierarchy. If it were, the following article could help: http:/A_2153.html

Instead, your data is "based on 2 fields called, position and location".

To automate that in Access, you basically create a report based on all employees except the president and the vice-president, grouped by location, sorted to show the manager first. Displayed in columns, this is the bulk of your "tree" (the four locations as lists, side-by-side). The report header can add the names of the president and the vice-president above that.

It really doesn't look that complicated, and if you can avoid managing a real tree, so much the better!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd need to store your data in a relational fashion, of course.

lEmployeeID (AutoNumber, PrimaryKey)
etc etc

lEmpTypeID (AN, PK)

tEmployeeType would contain items such as Employee, Manager, Vice President, etc.

You would then store the "manager" for each employee with that record. From there, you could use several different methods to create your hiearchal tree (but Access doesn't have any builtin "tree" mechanisms).

jfer0x01Author Commented:
Any other Office Products?

Perhaps VISIO, Project or an Excel Function?
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Jim P.Commented:
Don't hold me to this, but I think Vizio can do a front-end to an Access db to build such a tree.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Visio can interact with Access, but like Jim said, I'm not sure that if could work tightly enough with Vizio to do what you're after. Visio is VBA-enabled, so you could possibly automate it, but I've never done so (and don't know anyone who has).

You could fudge this in Access with listboxes and such ... I've done something similar a looong while back, and while it was somewhat crude it accomplished what was needed and the client was happy with it. It looked a lot like the Relationship diagram in Access, but wasn't interactive (i.e. you couldn't move the listboxes and such around).
jfer0x01Author Commented:
Ill Stew on this for a while.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Thanks. We'll await your response.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.