Stumped on an Org Chart!

[BullHalseyUSN]

Hi,

I need to think about how I might create an org chart in Access (we have 2000).

The user environment here is restrictive. I could not, for instance, install a solution like CrystalReports. Also, we don't seem to have access to any kind of webserver, so I can't use DAPs as far as I can tell.

The information and structure of the database itself is typical. Name, title, etc.

How could I, using pretty much only access, display an org chart? It's a very simple chart, but I can't even begin to think about how to approach the problem. Report? Not that I can think of. Form? Well maybe.

thoughts, please.

Thanks!

 

[dalenz]

Hi Bull,

Couple of thoughts,

If you have a fixed hierachy then the following could work.

Assign each person a level and postion for displaying on the form.

Box centered at the top of the form would L01P01
Underneath 2 Boxes L02P01 and L02P02
Underneath 4 boxes L03P01 L03P02 L03P03 L03P04

If you need more flexiblity then you to think along the lines of setting up relationships of departments,supervisors and who reports to which supervisor. (civilian terms) That way you can sort and group before displaying.

 

[BullHalseyUSN]

Hmmm...thanks.

There is actually a joint military hierarchy that may be suitable to this. May get complicated as we do not use the terminology ourselves.

All departments have a boss, an assistant boss, then underlings. Nice and orderly.

 

[dalenz]

If it's nice and orderly then it suits setting up a org chart.

Requires setting up a table of that structure and then linking that back to your crew table.

 

[BullHalseyUSN]

So I would make a table like this for each dept perhaps?

Dept Name
|
Dept Head
|
Assistant Boss
|
Underling 1
|
Underling 2

Tks for your thoughts.

 

[MichaelRed]

If you have the MS "Subscription", then their VISIO product has a complete soloution, starting with the chart and creatiing an MS. Access table of the org. Of course, it can be done the other way as well. The org chart table is all-in-one, simply having an "ID" number for each member (Unique) and a "ReportsTo" field. Each member's "ReportsTo" field , obviously has the member's immediate supervisor's "ID".

A "chart" in Ms. A. would be somewhat of a challenge, particularly in the dynamic generation of the lines of authourity / reporting, and it could be quite daunting to place an entire org on a single report of more than a few departments. I might try to do this with a seperate sub-report (on a seperate sheet) for each sub-branch of more than perhaps three departments, but even with this arrangement, it would be an exercise in mapping the cross-references between the sheets.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BullHalseyUSN]

Michael,

Thank you.

Unfortunately, we neither have a Visio license nor the ability to add software to our machines, which are locked down tighter than a frog's rear.

However, that "reports to" structure is a useful thought.

Thanks.

 

[JeremyNYC]

Another thought is to look into using a treeview control for this. Treeview controls look like the Folders pane you can display on the left side of Windows Explorer. You would be able to expand and collapse different branches, which might make up for the less than gorgeous graphic look of the treeview.

I haven't used one of these for a long time, and that was in VB, not Access, so I won't be able to give you much more to go on with this, but it might be worth looking into.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[at51178]

Hey bull

I had come across the same situation you had but I need to know what Kind of information you need ot put in the org chart so that I could help you further.

 

[BullHalseyUSN]

Basically the way the org chart works in a facility such as this is as follows.

There is a CO (commanding officer) then an XO (exec officer) as well as a senior enlisted leader. These folk are roughly equivalent to an executive team.

Then there are departments, such as Medical. Each department has a boss, an assistant boss, then clerks.

Each position has a code. For instance CO owns the code "00." Doesn't matter who the individual is. The org chart also needs to display the member's rank.

I'll put this in Army terms since it may be more familiar:


Col Sherman T. Potter <- masthead level
Commanding Officer - 00

|
---------------------------------
|
MEDICAL DEPARTMENT <- department level
Dept Head: Captain Hawkeye Pierce - M1
|
Leading NCO: CPL Maxwell Q. Klinger - M2
|
PFC Igor - M3
|
PFC Minion - M4

 

[dalenz]

Bull,

Depending on how many depts, a form and subforms could work.

Subform for each dept:

Header

Department
Dept Head
Leading NCO

Detail
continuous form sorted by code for PFC's

Main form:

Header

CO
XO
SEL

Detail

Postion the subforms across the form.

Dale

 

[BullHalseyUSN]

I could do a similar thing with reports/sbrpts...thanks!

I am struggling with the tables and their relationships.

I have one table called members, and it has a text field for &quot;title&quot; into which I was just planning to type things like &quot;commanding officer&quot; into a field called &quot;title&quot; and also to have a departments field.

I am kind of stumped overall. How do you think the departments element can be handled, table and relationship wise?

Many thanks.

 

[dalenz]

Bull,

A lot depends on wether or not you are using existing table or creating new ones.

I would need to know a bit more about the tables you are using and whats in them to be of real assistance.

If new ones you may want to set up a few lookup tables and use combo boxes when entering members, ie

tblDepartment. 2 fields DeptID and Department.
The ID could be 01 - 99 or ME for medicial. Easier to select on in queries than having to type the full description

The advantage of a combo is it forces a standard selection and cuts out errors such as misspelling or abbreviations.

You could also have a tlbRank as I'm sure that's a very defined description.(can't have British spelling of Lieutenant)

In terms of relationships it back to the one to one, one to many.

A member can only have one dept and one rank

A dept can have many menbers many ranks.

If you can give me a bit more detail I'll be happy to have a look.

Dale

 

[MichaelRed]

Hopefully, you can copy the following into some text editor/processor (NotePad / WordPad) and get a reasonable view of the content. It represents an org chart derived from VISIO, and a couple of the fields may not be applicable w/o that app, however the RptToId and ID fields clearly and easily show the entire organizational relationship hierarchy. The names of individuals,departments, and positions are somewhat incidental but convienient to the generation of any 'report'. In a truly structured organization, these coulds/woulda/shoulda be in seperate tables linked through the ID field. Replacement/reassignment of an individual is accomplished simply through a couple of Update queries. Additions/deletions to the organition are append or delete queries with the relevant info. In adding or deleting any individual you need to, of course, be quite careful that any any 'departmental' relationships are appropiatly ammended. This is particularly necessary if a 'supervisory' position is removed. Failure to re-assign or remove any which report to the removed position become orphaned and often will not be returned in simple self-referential queries.

The BOLD &quot;line&quot; is the column headings of hte table. If you paste this into a text editor, all of this should be on ONE line and the other info should line up in columns below them.

[bold
ID Name StartDt StartA_P Position RptToId ReportsTo Department Telephone E-Mail MasterShape Loc JobCode[/bold
1 Ron Duvall No CEO The Duvall Group Corp 1
2 Brian Duvall No COO 1 Ron Duvall Operations DC 2
3 Brett Duvall No CTO 1 Ron Duvall Technology Corp 2
4 Russ ? No CFO 1 Ron Duvall Finance Corp 2
5 Chief Marketeer No CMO 1 Ron Duvall Marketing Corp 2
6 Mr. Sales No V.P. Sales 1 Ron Duvall Sales Corp 2
7 Ron's Scy No Exec. Asst. 1 Ron Duvall The Duvall Group Corp 5
8 Brett's Scy No Off. Asst. 3 Brett Duvall Technology Corp 6
9 Brian's Scy No Off. Asst. 2 Brian Duvall Operations DC 6
10 Mkt. Scy. No Off. Asst. 5 Chief Marketeer Marketing Corp 6
11 G. Sibley No V.P. Finance 4 Russ ? Finance Corp 6
12 Fin. Scy. No Off. Asst. 4 G. Sibley Finance Corp 6
13 Mktg. East No V.P. Mktg 5 Chief Marketeer Marketing Corp 3
14 Mktg. West No V.P. Mktg 5 Chief Marketeer Marketing Corp 3
15 M. Red No Dir. Sftw. Devel. 3 Brett Duvall Technology Corp 4
16 Programmer No Programmer 15 M. Red Technology Corp 7
17 Sftw. Test No Tester 15 M. Red Technology Corp 7
18 Sftw. Inst. East No Installer 15 M. Red Technology Corp 7
19 Sftw. Inst. West No Installer 15 M. Red Technology Corp 7
20 Sftw. Cust. Svc. No Software Cust. Service 3 Brett Duvall Technology Corp 7
21 Cust. Sftw. Trng. No Software Training 3 Brett Duvall Technology Corp 7
22 Network Administration No Net. Admin. 3 Brett Duvall Technology Corp 7
23 Dir. Cust. Support No Director 2 G. Sibley Operations DC 7
24 Mgr. Cus. Supp. 1 No Cust. Supp. Manager 1 23 Dir. Cust. Support Finance DC 7
25 Mgr. Cus. Supp. 2 No Cust. Supp. Manager 1 23 Dir. Cust. Support Finance Dc 7
26 Mgr. Cus. Supp. 3 No Cust. Supp. Manager 1 23 Dir. Cust. Support Finance DC 7
27 Mgr. Cus. Supp. 4 No Cust. Supp. Manager 1 23 Dir. Cust. Support Finance DC 7
28 Dir. D.C. No Dir., Dist. 2 Brian Duvall Operations DC 7
29 Mgr. D.C. 1 No Dist. Mgr 28 Dir. D.C. Operations DC 7
30 Mgr. D.C. 2 No Dist. Mgr 28 Dir. D.C. Operations DC 7
31 Mgr. D.C. 3 No Dist. Mgr 29 Dir. D.C. Operations DC 7
32 Clerk A.1 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
33 Clerk A.2 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
34 Clerk A.3 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
35 Clerk A.4 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
36 Clerk A.5 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
37 Clerk A.6 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
38 Clerk A.7 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
39 Clerk A.8 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
40 Clerk A.9 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
41 Clerk A.10 No Clerk 24 Mgr. Cus. Supp. 1 Finance DC 8
42 Clerk B.1 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
43 Clerk B.2 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
44 Clerk B.3 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
45 Clerk B.4 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
46 Clerk B.5 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
47 Clerk B.6 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
48 Clerk B.7 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
49 Clerk B.8 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
50 Clerk B.9 No Clerk 25 Mgr. Cus. Supp. 2 Finance DC 8
51 Clerk B.10 No Clerk 26 Mgr. Cus. Supp. 2 Finance DC 8
52 Clerk C.1 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
53 Clerk C.2 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
54 Clerk C.3 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
55 Clerk C.4 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
56 Clerk C.5 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
57 Clerk C.6 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
58 Clerk C.7 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
59 Clerk C.8 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
60 Clerk C.9 No Clerk 26 Mgr. Cus. Supp. 3 Finance DC 8
61 Clerk C.10 No Clerk 27 Mgr. Cus. Supp. 3 Finance DC 8
62 Clerk D.1 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
63 Clerk D.2 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
64 Clerk D.3 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
65 Clerk D.4 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
66 Clerk D.5 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
67 Clerk D.6 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
68 Clerk D.7 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
69 Clerk D.8 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
70 Clerk D.9 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8
71 Whse A.1 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
72 Whse A.2 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
73 Whse A.3 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
74 Whse A.4 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
75 Whse A.5 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
76 Whse A.6 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
77 Whse A.7 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
78 Whse A.8 No Warehouse 29 Mgr. D.C. 1 Operations DC 9
79 Whse B.1 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
80 Whse B.2 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
81 Whse B.3 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
82 Whse B.4 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
83 Whse B.5 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
84 Whse B.6 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
85 Whse B.7 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
86 Whse B.8 No Warehouse 30 Mgr. D.C. 2 Operations DC 9
87 Whse C.1 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
88 Whse C.2 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
89 Whse C.3 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
90 Whse C.4 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
91 Whse C.5 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
92 Whse C.6 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
93 Whse C.7 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
94 Whse C.8 No Warehouse 31 Mgr. D.C. 3 Operations DC 9
95 Clerk D10 No Clerk 27 Mgr. Cus. Supp. 4 Finance DC 8





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BullHalseyUSN]

Great suggestions, guys! Thanks for the support.

What I wound up doing was making an option group for DEPT HEAD. Based on the value of that, you would be assigned a hierarchy in your DEPT. Worked pretty well.