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