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

    Change of DB structure results in challenging report

    Experts:

    I had posted a question on DB design in this forum yesterday. I had attached database (now called "Organizational Hierarchy -- v01 (Before).mdb) and I inquired advice as to how I can link an employee table to the 3-tiered design (please see relationship diagram in the DB).

    Long story short, multiple experts strongly discouraged me from using a 3-tiered design and suggested to move all three entities (records on Principal, Directorate, and Branch level into a single table). Ultimately, it was suggested that -- through adding a "HigherLevelOrgID" -- I could output the same result. This change in the structure is captured in the 2nd DB (now called "Organizational Hierarchy -- v02 (After)").

    Ultimately, I definitely can already see the benefits of having placed all offices (regardless of level) into the same table. However, at this time, I'm struggling to replicate a report in v02 (very simple in v01).

    In v01, I have the following:
    - Query 2 -- which shows the "Office Name" from all 3 tables.
    - Report T1_Organization_Principal" is then linked Query 2 (with applied "grouping format").
    - The outcome is a report that shows the offices across the three (3) levels (in 3 columns).
    - ... this worked great.

    Now, in v02, I've done the following:
    - In the select query, I added the same table [T1_Organization] three times... giving me [T1_Organization], [T1_Organization_1], [T1_Organization_2]
    - I then added two (2) left join from "HigherLevelOrgID" to the "OrgID"...
    - Now, at first glance, it appears as if I have the same output as DB v01 gave me. However, when looking closer at the records, that's no longer the case... for instance, "Office of the CEO" (Principal level) is stored in all three (3) columns.
    - Thus, rptQuery2, doesn't even come close to mimicking the report I have in DB v01

    My question: Although I now have embraced the idea of changing my 3-tiered design to a single-table design for the various org levels, I'm now struggling w/ creating a grouped report showing me the distinct "parent" | "child" | "grandchild" relationships. How can I modify my DB v02 query and/or report to give me a similar report outputted by the report in DB v01?

    Thank you!!
    EEH


    P.S. The post where which led to the change of db is as follows:
    https://www.accessforums.net/showthread.php?t=77615
    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
    Query doesn't include fields textboxes are bound to.

    Don't do grouping or sorting in query.

    SELECT T1_Organization.*, T1_Organization_1.*, T1_Organization_2.*
    FROM (T1_Organization LEFT JOIN T1_Organization AS T1_Organization_1 ON T1_Organization.HigherLevelOrganization_ID = T1_Organization_1.Organization_IDpk) LEFT JOIN T1_Organization AS T1_Organization_2 ON T1_Organization_1.HigherLevelOrganization_ID = T1_Organization_2.Organization_IDpk;
    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 - thanks for the follow-up...

    I realized the following SQL works when adding criteria = "Branch" on the last table instance:

    SELECT T1_Organization_2.OfficeName AS Principal_OfficeName, T1_Organization_1.OfficeName AS Directorate_OfficeName, T1_Organization.OfficeName AS Branch_OfficeName
    FROM (T1_Organization LEFT JOIN T1_Organization AS T1_Organization_1 ON T1_Organization.HigherLevelOrganization_ID = T1_Organization_1.Organization_IDpk) LEFT JOIN T1_Organization AS T1_Organization_2 ON T1_Organization_1.HigherLevelOrganization_ID = T1_Organization_2.Organization_IDpk
    GROUP BY T1_Organization_2.OfficeName, T1_Organization_1.OfficeName, T1_Organization.OfficeName, T1_Organization_2.Organization_IDpk, T1_Organization_1.Organization_IDpk, T1_Organization.Organization_IDpk, T1_Organization.Org_Level
    HAVING (((T1_Organization.Org_Level)="Branch"))
    ORDER BY T1_Organization_2.Organization_IDpk, T1_Organization_1.Organization_IDpk, T1_Organization.Organization_IDpk;

    Again, thanks for the help. I'm glad you convinced me that this structure will work better (vs. multi-tiered relationships). Now I just have to figure out how to create a form where users will enter the correct "HigherLevelID" into the correct record. This is now a manual process (vs. the automated auto-linking before). Any recommendations how to best add new directorates and branches and linking them correctly?

    Thanks,
    EEH

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please explain this with example.
    a form where users will enter the correct "HigherLevelID" into the correct record.

    If the user is entering data, you could give user the name of the "Higher office" and , based on the user's selection, through code - insert the "correct" higher level ID, and even ask them to verify their choice. That seems to reduce possible errors, unless of course the user knows nothing of the org hierarchy structure.

    My observation is that you are determining HOW you might do things, rather than focusing on what needs to be accomplished. I don't mean that as negative, just an observation that focused more on how than what. And you're not the first --there are many. What happens (hypothetically) if a new team comes in and renames the levels in the Org?
    Does your current database allow for employees to change positions? For example, can the CFO be swapped with the COO?
    Again these are questions based on very limited knowledge of your business, and are not meant as negative criticism. They're meant to question the structures you have and foresee to ensure your tables and relationships support your needs.
    Good luck with your project.

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

    You made some excellent points and I certainly appreciate the feedback. We're still at the beginning stages of the redesign so these questions will have to be addressed soon. The plan is to create a table including all positions ("spaces")... to be linked to the OrgTable. Afterwards, via an employee table ("faces"), I want to tie in personnel. That said, if there's an organizational change (i.e., a branch is moved from directorate to another), this procedure should be simple... merely update the HigherLevelID. Naturally, staff members will always move... either laterally, upwards, or else. So, having a relationship between position and employee would, I hope, account for that.

    All that said, after we addressed the "what", we'll focus on the how. Again, thank you for providing additional feedback... 'very much appreciated.

    EEH

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @tom

    I think you have one data error in your new table organisation table - the office of the ceo is at the top of the pile so there should not be a higher level organisation - it should either be null or 0. Otherwise it is 'reporting' to itself. Down the road you may find this gives you a problem

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ok... thanks for the feedback. I will change it accordingly.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As for data entry of new records to T1_Organization, could use cascading combobox on form. If user selects OrgLevel of Branch then only want Directorate items to choose from for HigherLevel, if user selects Directorate then only want Principal items to choose from for HigherLevel.
    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.

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Great recommendation... I will try it out.

    Thanks!

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

Similar Threads

  1. Replies: 1
    Last Post: 07-31-2018, 05:06 AM
  2. Replies: 15
    Last Post: 03-05-2015, 03:30 PM
  3. Replies: 4
    Last Post: 01-21-2012, 01:21 AM
  4. Back with a more challenging query
    By satswid in forum Access
    Replies: 0
    Last Post: 12-05-2011, 03:53 AM
  5. Challenging design
    By Zoroxeus in forum Database Design
    Replies: 0
    Last Post: 01-30-2006, 11:27 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