Hello Experts:
Over the past few weeks, I posted several questions pertaining to "dual listboxes". As a result of the fantastic help in this forum, I now have a process which allows me to assign "billets" (i.e., jobs and their associated staff members/employees) to cross-functional teams or CFTs. The attached database demonstrates the process... it works great!
At this time, however, I'd like to further expand/tweak the existing process to have little bit more flexibility when assigning billets/employees to CFTs. Allow me to recap the process using a generic example:
Recap:
- The organization has seven jobs (billets). Only five of these jobs are filled -- the other two are vacant.
- The five employees are: John Wayne, Richard Roundtree, Henry Fonda, Clint Eastwood, and Joan Crawford.
- The five employee's positions/jobs are (respectively): accountant, data analyst, director, financial analyst, and marketing analyst.
- Now, the organization wants to assign these staff members (employees) to "cross-functional teams" (CFTs) such as of working groups, boards, planning groups, etc.
Existing Process:
- Bringing up the dual listbox (via the form) allows me to select any CFT (CFT #1, CFT #2, CFT #3, and so on) from the drop-down menu.
- Then, I can add/remove any or all staff members to any of the existing CFTs.
- Well, again, I really assign the "billet" (director, accountant, data analyst) -- not the person -- to the CFT. Note: Yes, I understand, "chairs" to attend meetings... "faces" do. Anyhow, that's how it works for now.
- Conceptually, all billets/jobs (and thus the employees in those positions) can be assigned to all four CFTs, creating 20 records in the junction table.
- So far so good... again this works great.
Desired Process Expansion/Tweaking:
- As stated before, I'm assigning the "billet" (e.g., Financial Analyst") to a CFT.
- And again, through that process, I assign e.g., "Clint Eastwood" to CFT #1 or #2 or else (always showing the "Financial Analyst" position in a report).
- However, while Clint Eastwood is a financial analyst, he may serve as the "project lead" on one CFT while serving in a totally different capacity on another CFT.
- Further, employee Clint Eastwood may function as the "scheduler" in CFT #1 today but he could serve as the "CFT #1 Lead" next month.
- The same applies to any of the other staff members. I assign the billet (and inherently their job title) to the CFT, but any of the employees could offer subject matter expertise outside the regular job title.
That said, to accommodate modifying the process, I can image that another table would have to be created first. In the new table -- linked to the StaffMembers' table -- all CFT functions in which an employee could serve would be added.
My question(s):
- When bringing up the dual listbox form and I want to add "Clint Eastwood | Financial Analyst| as a "Lead" to CFT #1, how would that **sub-select** of function (not job) be accomplished?
- Maybe I would not necessarily have to see "Lead" in that form (even though it would be great), but if another form is required (2nd step is kinda convaluted though) how can I easily change his CFT function depending on which CFT he attends and where we stand in the project (again, he might obtain seniority as time goes on)?
Please keep in mind that the actual management of employees and job functions is based on, e.g., 2000 employees with maybe 2-3 different possible CFT functions/roles.
Thank you,
EEH