• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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?
1 Solution
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?
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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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).
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!

jfer0x01Author Commented:
Ill Stew on this for a while.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Thanks. We'll await your response.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now