Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Link employees to 3-tiered organization

    Experts:

    I need some assistance/recommendations for properly setting up relationships in an Access database. Attached is a "high-level" database containing four (4) tables.

    1. There are three (3) tables describing the organizational hierarchy:
    Organization_Level_1 -- Chief executive level
    Organization_Level_2 -- VP level
    Organization_Level_3 -- Branch level

    ... the actual organization goes down even further on the "hierarchy"; however, this structure should be sufficient for demo purposes.

    2. Next, table "Employees" will store all employees (regardless of seniority).




    Now, my question.. how can I link the employee table to the other three levels of organization where I then can run a query/report and, e.g., output all "branches" (level 3) and the associated employees. Similarly, I want be able to do the same for the first two levels (chief executive and VP level).

    Should I add another field in the employee table and add the same to all of three organizational tables? Naturally, employees may get promoted and thus "move up" a level. The structure of the database must be flexible enough to accommodate any lateral or vertical employee changes.

    Given that I have limited DB background, it would be great if someone could download the SAMPLE db and modify the base structure (add key fields). THOUSAND THANKS in advance.

    EEH
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 - yes, in principle, the "org chart" (top mgmt, department head, section head) follows the same hierarchy I need to mimic. I downloaded the Access db but it only had a single table.

    I definitely will need to maintain the "levels" in separate tables. I am flexible on tying in the employee table... I am totally open to suggestions. Attached is an updated version of my db. Although the 3 sample queries output the correct employees, I manually entered the "foreign key" reference ID numbers into each of the org tables. Not sure whether or not this is the "smoothest" way of doing business.

    Not sure if the linkage can be created properly when using a form and adding new employees and/or updating existing information (e.g., if existing CEO retires and new/existing employee takes over).

    I truly welcome any recommendations that would allow me to maintain integrity of the data when using a data entry form.

    Thanks,
    EEH
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Instead of 3 tables, consider 1 table with another field for LevelCode.
    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.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm with June on this - your structure doesn't look right to me.

    Your employee table should contain a LevelFK and a BranchFK.
    Personally I dispense with Level and would add a field for Line Manager ID, this will allow you to build a hierarchy with any number of levels. The LineManagerID would store the EmpID of that employees manager.
    This would self contain the whole structure.

    Any time you are naming a table or a fields with a suffix like _1 _2 etc it is a warning bell that your data isn't normalised.
    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 ↓↓

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 -- if you suggest placing all 3 levels of the organization into a single table, then I have to completely disagree with you. Essentially, afaiac, this would go against the concept of normalizing data.

    Minty - I'm curious to learn more about you concept/idea. As I am a visual person, I'm not fully tracking on this structure. Would you be willing to upload a basic/structural sample db? Thank you!


    EEH

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a look at this post / thread https://www.accessforums.net/showthr...608#post427608
    See if that clarifies it.

    If not I can probably knock something up to try and explain better.
    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 ↓↓

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Minty -- that sounds great. I am still not clear on how to tie in the employees to the particular org level. Pls keep in mind that each table starts with auto ID number 1. So, even if I use a UNION query on the 3 org levels, I then have duplicates in the org ID level.

    Any recommendations as to how I can tie, e.g., "Commodore Stone" to the Org_Level_1 table which, e.g., CAPT Kirk would have to be linked to the 2nd tier table?

    Thx,
    EEH

  9. #9
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure i understand the need for the separate Org Level tables.
    I can't see how they assist you or what the will achieve in any way?

    Surely a single table with all possible roles / levels in it and a field in your employee table to store that accomplishes the same thing?

    Perhaps you could expand on what purpose they serve, or what you think they will achieve?
    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 ↓↓

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I redesigned my database and given the changes in the question, I posted a new thread:

    If you are curious, please check it out at: https://www.accessforums.net/showthr...809#post434809

    Thanks

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Think of the [State] and [City] concept. There's a cascading relationship between these two entities.

    That is, if I were to select state = CA (California), I want to see cities such as Los Angeles, San Diego, California. I shouldn't see values such as Miami or New York City. Applying a cascading relationship between these tiers mitigates errors and makes it more use-friendly when employees have to select values.

    Now, if you were to upload a sample database that follows your logic, I'd welcome to have another look. Anything else is merely a philosophical discussion and won't help me addressing my question.

    Thank you for your continued assistance.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Your analogy is faulty. You would not have a table for each state, territory, protectorate - that would be 50+ tables all with identical structure.
    If the U.S. ever annexed another such entity, that would be a major modification of db structure (tables, queries, forms, reports, code).
    Should be one table and apply filter criteria to view only CA cities. What if you wanted to see cities from ID and CA and WA? How would you query that if multiple tables are used? Another aspect of normalization is not duplicating structures for same type of data.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."
    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.

  13. #13
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 -- no... , it would be two tables ([City] linked to [State]).

    To convince me otherwise, I would have to see an ** EXAMPLE ** structure. Would you be willing to post it?

    Thanks,
    EEH

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    And how would you link Cities to States - Cities would have a field StateCode. States would be source for a combobox to assist with populating Cities and States could have other info about states. This is nothing like your db where Employees links to 3 tables. And those 3 tables are hierarchically linked 1 > 2 > 3. You already acknowledge this structure is not working, otherwise you would not be here. You might research recursive database design. This is what family tree and organizational structure and manufacturing assembly type databases are. Involves table self-joins in query. Recursion design not easy to work with in Access, other db platforms such as MySQL and SQLServer handle better. However, link I provided earlier was Access solution for handling recursive data. Following is your data restructured as suggested by myself and others.

    LevelID LevelName
    1 Principal
    2 Directorate
    3 Branch

    OrgUnitID LevelID OfficeName HigherOrgUnitID
    1 1 CEO 0
    2 1 CFO 0
    3 1 COO 0
    4 2 ABC VP 1
    5 2 KLM VP 1
    6 2 XYZ VP 1
    7 2 DEF VP 2
    8 2 GHI VP 2
    9 2 JKL VP 3
    10 2 MNO VP 3
    11 3 Branch ABC 1 4
    12 3 Branch ABC 2 4
    13 3 Branch ABC 3 4
    14 3 Branch KLM 1 5
    15 3 Branch KLM 2 5
    16 3 Branch KLM 3 5
    17 3 Branch KLM 4 5
    18 3 Branch XYZ 1 3
    19 3 Branch XYZ 2 3
    20 3 Branch something 1 4
    21 3 Branch something 2 4
    22 3 Branch something else 1 5
    23 3 Branch something else 2 5
    24 3 Branch other stuff 1 6
    25 3 Branch other stuff 2 6
    26 3 Branch more stuff 1 7
    27 3 Branch more stuff 2 7
    28 3 Branch more stuff 3 7
    29 3 Branch more stuff 4 7

    EmployeeIDpk EmployeeNumber LastName Position UnitID
    1 111 Smith Branch Manager 11
    2 125 Jones CEO 1
    3 145 Johnson Branch Manager 13
    4 163 Jackson Branch Manager 15
    5 140 Parker Analyst 12
    6 170 Albert CFO 2
    7 199 Herman COO 3
    8 148 Bernard VP 5
    9 139 Davids VP 4
    10 146 Zachary HR Manager 14
    Last edited by June7; 07-16-2019 at 08:26 PM.
    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.

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 - first of all, thank you for the continued assistance... I truly appreciate it.

    Now, wrt to the State/City example, attached .zip "Example_State_Zip" illustrates that process. But I'm side-tracking... not trying to solve State/City relationships.

    I did follow your advice and added an "Org_Level" such as 1, 2, or 3. Please see attached .zip "Employee Hierarchy" that has the redesign.

    What I'm trying to accomplish at this time is as follows:
    - In the "Employees" table (yes in a form as well), when clicking on the last field [Org_ID], the drop-down shows 28 possibility office values. 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".

    I figure you'll keep your stance that this design is not valid. If so, would you be willing to modify the simple db design to demonstrate your approach? I'm a visual person and "picture is worth a 1,000 words".

    Thank you in advance.

    EEH
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help with organization Newbie
    By dan@goditmaderanch.com in forum Access
    Replies: 5
    Last Post: 12-05-2016, 11:58 AM
  2. Selections based off of tiered criteria
    By New_2_Access in forum Access
    Replies: 7
    Last Post: 06-19-2012, 11:00 PM
  3. Auto Organization?
    By ProjectCamaro in forum Database Design
    Replies: 9
    Last Post: 01-18-2012, 07:00 PM
  4. Report organization
    By kstyles in forum Reports
    Replies: 7
    Last Post: 01-10-2011, 02:47 PM
  5. Organization help needed
    By Logan in forum Database Design
    Replies: 2
    Last Post: 07-30-2010, 11:00 AM

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