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