Results 1 to 13 of 13
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Employee Hierarchy

    Hello Experts:




    I need some assistance w/ either a) designing a query or b) slight modification of my database (pls see attached).


    BACKGROUND... the database includes the following objects:
    A. Three (3) tables that mimic a 3-tiered organization:
    1. T0_Level_1 (Principal level = highest level)
    2. T0_Level_2 (Directorate level = 2nd highest level)
    3. T0_Level_3 (Branch level = 3rd highest level)
    ... pls note that actual organization goes beyond the branch level, but for demo purposes, 3 levels should suffice though.


    B. There is one (1) table including all employees (again, for demo purposes, it's only a subset of the employees). It is important to note the following fields though:
    1. [LastName]... the employee's name
    2. [ReportsToEmployerID]... a lookup field with 3 columns
    3. [Org_Level]... a lookup field pointing to table [Lookup_Levels]
    4. [Org_ID]… I am having a challenge w/ this field.


    C. There are two (2) queries:
    1. Union query "Q1_Union_AllOrgLevels_Unsorted"... it ties all "organizational entities" (with OrgLevel 1:3) and (Org_ID number) into a single table.
    2. Select query "Q2_Select_AllOrgLevels_Sorted"... uses the union query and sorts it in ASC order by OrgLevel and Org_ID.


    Here's what I need some help with in the [T1_Employees] table. Allow me to recap the process in procedural order.
    - When a new employee joins the organization or "moves up" in the organization, I want to be able to pick the desired "Org_ID" value.
    - Now, remember the union query... it generated twenty-eight (28) currently existing "offices" within the organization. Reality is that actual db probably has something in the hundreds.
    - Ok, so when a new employed joins the organization, the user **knows** (that's an assumption) the organization level (1, 2, or 3) that this employee will work on. That is, if a chief executive is hired, we know
    that he/she will work on "level 1". Alternatively, if a manager is hired, level 3 is chosen.


    ... so far so good...


    - Now, again, when clicking on the last field [Org_ID], the drop-down with the 28 possibility comes up. This is what I need to solve!!
    - That is, if "Org_Level" = 1 is selected, I only want to see three possible values: "Office of the CEO"; "Office of the CFO"; "Office of the COO".
    - Similarly, if "Org_Level" =2 is selected, I only want to see/allow to select from the various "VP offices". Same concept if "3" is chosen... I only want to see "branches".



    Summary:
    - I am constrained wrt to the 3 tiers. They will be (and should be) kept in distinct tables to ensure data normalization.
    - I would image there's isn't a real issue with the employee table other than how to store/reference the [Org_Level] and [Org_ID].
    - Naturally, I am flexible on the mechanism for the [Org_Level] and [Org_ID]... whether it's a redesign of the table lookups or redesign of the queries, I'm ok.


    I'd truly appreciate if someone could assist me in streamlining the described [Org_ID] in table [T1_Employees] so that I only those entities shown based on the selected [Org_Leve].

    I apologize if I repeated myself or assumed certain information. In case of any questions, pls don't hesitate to ask for further details.

    Thank you,
    EEH
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am constrained wrt to the 3 tiers. They will be (and should be) kept in distinct tables to ensure data normalization.
    I don't agree with this statement - as has been mentioned in your other threads, this should be one table. You have provided zero reasons why they should be in separate tables.

    Either way this is what is causing your problem

    I've taken a look at your db and all you need to do is add your level 1 and 2 job titles to level 3 and change the link to the level3 PK. I don't have time to do it now, but if someone else doesn't do it for you in the meantime I'll take a look later tonight

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax:


    I am attempting to create a **relational** database... not a **flat file** (see attached JPG).


    Although I am not an expert, I know that putting all three levels into a single table will (and I have seen it in the existing version) create multiple instances of the same office when someone is updating the office name but doesn't apply the change to potentially hundreds of records that require the update.


    If I were to put all 3 levels into one table (pls find screenshot), I would replice values on the first and second tier over and over again. And, again, if I wanted to change value of "Principal Level: Office of the CEO" to, e.g., "CEO's office" I either have to make this change either six times manually or, alternatively, run an update query that then would update all matching records. Fact is, some users however don't use an update query and left us in
    the current predicament of mismatching data.


    Now, in the "hierarchical concept" (as will continue to use it), the value is changed only once in the "Principal" table. And, given that I'm linking the primary and foreign keys, data integrity is being preserved.

    From my perspective, there are many many more reason as to why a cascading approach offers some major benefits. Another example would be changing the reporting responsibility. If the VP of HR would be moved from CEO to COO, a flat table require that I update values across all corresponding fields. Alternatively, the cascading approach required one change. That is, change [PrincipalID_fk] from "1" to "3" in in the directorate table and all of the associated child level entities will follow along.
    Now, you may disagree w/ this approach but we will have to agree to disagree. Again, I welcome any constructive feedback based on the question I asked.

    Thank you,
    EEH
    Attached Thumbnails Attached Thumbnails Organization_Flatfile.JPG  

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Ajax. Hierarchy/taxonomy in a single table.
    See Allen Browne sample
    I had an example in thread about dogs https://www.accessforums.net/showthread.php?t=64885
    on page 3 ~post 34

    Good luck with your project.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange... thanks for chiming in. The thread you referenced is very long and I was not able to locate the example. If you won't mind, pls post a direct link.

    While I'm not opposed to a changing the redesign, I really need to see an example. Below are some reasons that made me choose the multi-tiered structure.

    - There are six levels within the organization.
    - The db must be flexible enough to "move" e.g. an entity on the 4th tier and make it a "child" of another 3rd level parent entity. If so, all sub-entities on the 5th and 6th level will follow. Using the primary key/foreign key relationship (auto number on 3rd level is linked to number on 4th level), allows me to make that move with a single change from e.g., ID=17 to ID=28. Very simple process.

    So, I'm still baffled as to why members in this forum are so very much opposed to having several one-to-many relationships.

    Again, if you pls could attach your example db, I'd gladly check it out.

    Thanks!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link directly to the post I mentioned. It is an example table and related query in context of the dialog with the poster.
    Your other thread re Link employees 3 tier org has referenced links from others also.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I did look through another test database and found Table representing a hierarchy. It's a table of chemical compounds showing an entry and identifying its parent. It's strictly a mock up.

    Here is the table design
    Click image for larger version. 

Name:	CompoundTabledesign.png 
Views:	33 
Size:	7.0 KB 
ID:	39105

    Here is a sample of the data in tblCompound
    Click image for larger version. 

Name:	CompoundTableData.png 
Views:	32 
Size:	41.9 KB 
ID:	39106

    Here is sql of a query to display the hierarchy.
    Code:
    SELECT IIf(IsNull([parent compound]),Space(9) & [compound name],Space(9*[parent compound]) & DLookUp("[compound name]","tblCompound"," id =" & [parent compound]) & "  -->  " & [compound name]) AS CompoundHierarchy
    FROM tblCompound
    ORDER BY [tblCompound].[parent compound];
    And the output of the query showing hierarchy.

    Click image for larger version. 

Name:	CompoundHierarchy.PNG 
Views:	32 
Size:	38.8 KB 
ID:	39107

    As I said it is a mock up used in another post somewhere, but it has most of the concepts.
    Hope it helps.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So, I'm still baffled as to why members in this forum are so very much opposed to having several one-to-many relationships.
    We're not opposed to several one to many relationships, but the fact that you are splitting a single entity type into multiple tables

    Answer us this
    There are six levels within the organization.
    so what happens if you introduce a new level, or remove a level?
    The db must be flexible enough to "move" e.g. an entity on the 4th tier and make it a "child" of another 3rd level parent entity.
    what happens if a 3rd tier entity is removed and the associated 4th tier entities now move to a 2nd level parent?

    I've looked at your db and what I am proposing you appear to be effectively doing with employees - although you are using the dreaded lookups in your table design. see attached with tables called tblOrganisation and tblLevels.Employee Hierarchy.zip

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange -- thank you for posting the example... that was extremely helpful!!!

    I mimicked the concept in the attached DB "Redesign v02".

    Pls allow me to follow up w/ a few questions:
    - Query1 uses [HigherLevelOrganization_ID]... the equivalent of your ID. Given that this is an auto ID (1 through 10 for the directorate levels), there are too many indents.
    - Thus I used Query2 which uses the Org_Level (a number field with two columns in the lookup). This one uses the 3 levels from the lookup table. The problem is the "look of the query".
    - That is, Query2 output looks very much different (i.e., left/right indents or alignments) that Query1 output. How can that be fixed?

    And, finally, in the Employee table, I need to tie in the "Office Name" to each particular employee name. Right now, I added two fields [Org_Level] and [OfficeName]. [Org_Level] indicates, as the name suggests, which level the employee works on.

    ... so far so good... if [Org_Level] = "1" is selected in the the Employee table, then I need some mechanism that the drop-down in [OfficeName] to only give me three (3) options to choose from: CEO, CFO, or COO; if [Org_Level] = "2", then I only want to be able to see the seven (7) VP offices. Same for "3" where I only want to show (and be able to select from) the branch names.

    How can this latter be accomplished?

    Thanks,
    EEH
    Attached Files Attached Files

  10. #10
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If I can chime in from the other thread, you don't really need the Org_Level as you can work it out, from the top manager level down.
    In fact you would have to ensure that you kept this up to date when a manager / position / level changed. Simply calculate it.

    As described you can't do what you want directly in a table, and shouldn't try. Tables are for storing data not entering or editing.
    On a form what you require becomes relatively simple, using the cascading combo approach.

    Finally as others have advised, don't use lookup fields in tables, they obfuscate what is being stored, and actually make your life much harder in the long run. http://access.mvps.org/access/lookupfields.htm
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    From my post in other thread:

    What you describe is called cascading (or dependent) comboboxes. This requires VBA behind a form. Won't work with table. I NEVER build lookups in table, just comboboxes on form. This is a very common topic.

    So, with my table design, UnitID combobox on form would have a RowSource like: SELECT OrgUnitID, OfficeName FROM OrganizationUnits WHERE LevelID = [cbxLevelID];

    VBA in event procedure(s) would requery the combobox: Me.cbxOrgUnit.Requery

    Some prefer to set RowSource property SQL statement instead of just requerying combobox.

    The trick is figuring out what event(s) to put code into - usually form Current and combobox AfterUpdate and/or GotFocus.

    Unfortunately, this will probably mean a field in Employees for LevelID. Alternatively, use form/subform arrangement. Main form bound to OrganizationUnits and subform bound to Employees. Master/Parent Links set to the associated OrgUnitID and UnitID fields. UnitID will automatically populate with OrgUnitID value when creating employee record.

    Be aware cascading combobox with lookup alias doesn't work nice with continuous or datasheet form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 - thank you... I created a form and the cascading combo boxes work well. Appreciate the feedback in general.


    Much obliged!!
    EEH

  13. #13
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7:

    Based on the redesign of the db, I'm now struggling w/ creating a report that mimics the one of the one version. Pls see more details in the following thread:

    https://www.accessforums.net/showthr...954#post434954

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 05-12-2017, 04:09 AM
  2. Replies: 2
    Last Post: 05-09-2017, 04:12 PM
  3. Electrical Hierarchy
    By HelpDesk in forum Access
    Replies: 55
    Last Post: 06-26-2015, 06:51 AM
  4. Creating Hierarchy
    By Just_Some_Guy in forum Access
    Replies: 17
    Last Post: 07-24-2013, 07:06 AM
  5. Replies: 8
    Last Post: 06-06-2012, 12:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums