MS ACCESS - Polulate Organizational Structure based on employee positionand location

Posted on 2010-01-08
Last Modified: 2013-11-28

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?
Question by:jfer0x01
    LVL 84
    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).

    LVL 9

    Author Comment

    Any other Office Products?

    Perhaps VISIO, Project or an Excel Function?
    LVL 38

    Expert Comment

    by:Jim P.
    Don't hold me to this, but I think Vizio can do a front-end to an Access db to build such a tree.
    LVL 84
    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).
    LVL 58

    Accepted Solution

    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!

    LVL 9

    Author Comment

    Ill Stew on this for a while.
    LVL 84
    Thanks. We'll await your response.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now