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

    Dual Listbox -- process modification

    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
    Attached Thumbnails Attached Thumbnails CFTs.JPG   Staff Members.JPG  
    Attached Files Attached Files

  2. #2
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Tom!

    For the "back-end" level, create a new lookup table for the roles and add a new field to the JunctionTable(s) (say role_IDfk) as foreign key that points to the roles.

    After that, at "front-end" level, create a way to choose the rore into assigned RA_BINs and a lookup expression to get the last role for each staff member as default for the next assignment.

    Cheers,
    John

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John -- thank you for providing a great recommendation. I will attempt to implement your recommendation.

    Just to ensure I properly conveyed the process, allow me to recap.

    1. "Clint Eastwood" is a superstar employee... he's very busy and attends four CFTs.
    2. CFT #1 is held daily and he functions as the lead.
    3. CFT #2 is held weekly and he functions as the scheduler.
    4. CFT #3 is held ad hoc and he functions in yet another capacity.
    5. ... and so forth... you get the idea.

    Point is that there's no "last job function" for this employee (John: "... a lookup expression to get the last role...").

    Given multiple functions across the various CFTs, I just wanted to make sure that this may not result in a different concept.

    Thank you,
    Tom

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Tom!
    You're welcome!

    I noticed that, with the current design, you can't assign to a CFT a second employee with the same title, e.g. financial analyst. I think that you have to keep the ID of T00_JunctionTable_BCFT into the T00_JunctionTable_BCFT instead of BilletIDpk, unless your business rules impose the current structure.

    I attach your sample with a few changes on data structure (see the design view of Q50C_BilletsToCFTs) and some functionality for the roles (double click on an assigned staff member of the left listbox) to show you "how you could".
    Quote Originally Posted by skydivetom View Post
    Point is that there's no "last job function" for this employee
    Please, ignore the "last job function" feature of the attachment if is realy useless.

    Quote Originally Posted by skydivetom View Post
    Given multiple functions across the various CFTs, I just wanted to make sure that this may not result in a different concept.
    Forgive me but, the above is not so clear to me. Do you mean that you don't want to assign the same employee with the same role in different CFTs?

    Cheers,
    John
    Attached Files Attached Files

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

    I really like the changes you made. Please see two attached ERDs (current & proposed).

    I definitely want to integrate your proposed changes; however, I need to be able to "tie" billets to the CFTs. When looking at the ERD, I didn't see the BilletIDfk any longer. Based on the existing structure, how can I link billets to CFTs? Attached is the database with the table in the ERD view.

    Thank you,
    EEH

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John -- I really like your proposed structure. That certainly gives me the flexibility to have multiple "functions" joined with a job title.

    However, when looking the the modifications, I did not see how I can link billets to the CFT junction table. I know why you removed it (no dual assignments of the same BIN for e.g., incumbent and new employee who conduct turnover); however, I must be able to connect billets to CFTs.

    Please see attached the database with the tables added to the ERD + plus my current ERD and your proposed ERD. Again, I would love to make the transition you proposed but I must be able to connect billets and CFTS. Is there a work-around?

    Cheers,
    Tom

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

    Attachments

    Here are the attachments...
    Attached Thumbnails Attached Thumbnails ERD_Current.JPG   ERD_Proposed.JPG  
    Attached Files Attached Files

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Tom!

    You can connect billets to CFTs through the T00_JunctionTable_OBS. With current structure, you can't link multiple billets with a CFT, because, there is no way to find the related employees. Many employees may have the same billet, and a billet, is just a title of an employee.
    You can't assign jobs to a title, you assign jobs to persons.

    Here are some pictures:

    Click image for larger version. 

Name:	ERD_Flex.JPG 
Views:	30 
Size:	41.3 KB 
ID:	39483

    Click image for larger version. 

Name:	qryCFT_RolesFlexDView.JPG 
Views:	29 
Size:	40.2 KB 
ID:	39484
    Code:
    SELECT T00_JunctionTable_BCFT.CFTIDfk, T11_CrossFunctionalTeam.CFT, 
    T11_CrossFunctionalTeam.CFT_Category, T01_Billets.RA_BIN, 
    T01_Billets.RA_Billet_Title, T01_StaffMembers.FirstName, 
    T01_StaffMembers.All_LastName, tlkpRoles.roleName
    FROM tlkpRoles RIGHT JOIN (T11_CrossFunctionalTeam 
    INNER JOIN (T01_StaffMembers INNER JOIN (T01_Billets 
    INNER JOIN (T00_JunctionTable_OBS 
    INNER JOIN T00_JunctionTable_BCFT 
    ON T00_JunctionTable_OBS.OBS_IDpk = T00_JunctionTable_BCFT.OBS_IDfk) 
    ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) 
    ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk) 
    ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) 
    ON tlkpRoles.roleIDpk = T00_JunctionTable_BCFT.roleIDfk 
    ORDER BY T11_CrossFunctionalTeam.CFT_SortOrder;
    Click image for larger version. 

Name:	qryCFT_RolesFlex.JPG 
Views:	30 
Size:	55.1 KB 
ID:	39485

    Click image for larger version. 

Name:	ListCFT_RolesFlex.JPG 
Views:	29 
Size:	27.9 KB 
ID:	39486

    Cheers,
    John

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John -- wow... you're awesome. Final question on this topic...

    Given that I have hundreds (and thousands) of records in the system. I'm a bit leary that this major redesign result in losing the integrity of my current records. Just want to make sure that reassigning ID numbers won't cause a mix-up (on my part) w/ the records.

    Or do you think that there won't be much of an impact on the records themselves?

    Again, thank you for being an awesome supporter in this forum!!

    Tom

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Tom, you're welcome. I appreciate your kind words.
    I'm happy to help and, finally, I learn more from your questions than you take from my answers.

    About the redesign, in attend to avoide the disaster, first you have to ensure that, until now, each billet has linked with only one staff member in the T00_JunctionTable_OBS.

    If so, as shown at the attacmhent pictures, you have to join the CFTs with the T00_JunctionTable_OBS on BilletIDfk and replace the values of T00_JunctionTable_BCFT.BilletIDfk with the value of Primary key of T00_JunctionTable_OBS (OBSIDpk in my sample).

    Have a look:

    Before...

    Click image for larger version. 

Name:	Query_CFT_Before.JPG 
Views:	27 
Size:	55.2 KB 
ID:	39497

    Click image for larger version. 

Name:	CFT_Before.JPG 
Views:	27 
Size:	33.7 KB 
ID:	39498

    Execute this query:
    Code:
    UPDATE qryCFT_Billets2Members SET B_CFT = OBS_CFT;
    After...
    Click image for larger version. 

Name:	Query_CFT_After.JPG 
Views:	27 
Size:	59.9 KB 
ID:	39499
    Click image for larger version. 

Name:	CFT_After.JPG 
Views:	27 
Size:	31.3 KB 
ID:	39500

    Of course, before you do anything in your actual database, you have to get a lot of backups!

    I hope it helps. Good luck!

    John

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

    This project grows like a snowball and I'm have a tough time keeping up w/ the demands. At the same time, it's a very fun time and I truly enjoy it.

    All that said, I'm just getting back to your additional info. Thank you for that. Given that my naming convention is slightly different (e.g., table names and field names), I currently going through the process of updating those particulars.

    However, once I went through the frmRoles tabs, I also realized that I could NOT add any VACANT billet to my CFTs. Now, someone may wonder as to why a vacant billet (w/ no person in that position yet) should be added to the CFTs. Well, in some cases, it's merely for tracking purposes so that team members know that there's a person soon joining the team.

    Anyhow... too much information here. Please see attached JPG which illustrates the error I ran into when attempting to add a vacant billet. Was that by design? If so, can it be fixed so that vacant billets can be added? If a name (staff member is required), I have resolved that with adding a "dummy staffmember" with lastname = [VACANT].

    Thank you for the additional info.

    Tom
    Attached Thumbnails Attached Thumbnails Error.JPG  

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Tom!

    Software development is like free diving: the more you dive, the more you sink. That is a common "trap" of many human's activities.

    About the error, don't pay attention to the message, this is the only message in your error handling block of your cmdAddOne_Click() method.
    Code:
        Exit Sub
    ErrorHandler:
        MsgBox "You cannot assign the same two billets to a cross-functional team!", vbCritical, "File Error(" & Err & ")"
    
    End Sub
    About design, of course you can add vacant billets in CFTs table if there are stored in T00_JunctionTable_OBS (of the relevant sample).
    In picture bellow, you can see that in the three last records of T00_JunctionTable_OBS, the field StaffMemberIDfk is Null, but their primary keys are already added in table of CFTs normaly.

    Click image for larger version. 

Name:	VacantBillets.JPG 
Views:	20 
Size:	78.5 KB 
ID:	39521

    I hope that this solves this issue and drives you straight to the next.

    Cheers,
    John

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

    Wow... impeccable timing. I just finished (last 5 min.) the integration of your modified architecture in my database. I think I succeeded but, naturally, I will have to compare records/report between the "before" and "after" version to ensure I didn't mess up on the ID keys in the OBS and BCFT tables. Initial spot-checking indicates that all went well (I keep on crossing my fingers).

    Attached are the ERDs of the before and after version.

    I truly hope you won't mind my asking you another question. If you look at the old ERD, you'll notice that T11 (Crossfunctional Teams - CFT) and T21 (Operational Teams - OPT) are tied via the BCFT and BOPT into [T01_Billets].[BilletIDpk].

    Break

    Now, per your change recommendation (as illustrated in ERD New), T11 is tied into T00... BCFT which is then linked to T00...OBS. Now, I have not replicated the CFT process (multiple staff members into multiple CFTs) into the OPT process (multiple staff members into one and only one OPT). I'll do that tomorrow morning.

    My question: Do you foresee it to be a problem if I tie the OPT's RecordIDfk also into OBS's RecordIDpk? So, just like before, CFT and OPT are connected to the same field in the OBS table. I hope this makes sense.

    Thousand thanks for all of your help. I truly, truly appreciate it!!!

    Tom
    Attached Thumbnails Attached Thumbnails ERD_Old.JPG   ERD_New.JPG  

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Thousand thanks for all of your help. I truly, truly appreciate it!!!
    My pleasure.

    My question: Do you foresee it to be a problem if I tie the OPT's RecordIDfk also into OBS's RecordIDpk? So, just like before, CFT and OPT are connected to the same field in the OBS table. I hope this makes sense.
    Imagine the OBS table as a tank with staff members that can join in any number of teams. Each team-table, may have a RecordIDfk as reference to OBS table and the fields/properties relevant to team. Many team-tables may refers to OBS table just like a staff member can join in several working teams. If that is a problem or not, is dependant on your business rules. I hope this makes sense too.

    John

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

    Thank you... 'appreciate the additional info. I just finished tweaking the CFT process (multi-select across CFTs) into the OPT process (single select into one OPT). YEAH!!!!!

    It took little bit longer than planned but ultimately, it is working now. I only accomplished it based on your previous feedback as well as the solid architecture.

    There was only one thing I couldn't figure out. After I added a number of new roles (via the pop-up form), the listbox items are not sorted in, e.g., ASC order. That is, I could sort them in the data entry form but not in the listbox (after entering new items). See attached JPG. As I didn't see any data source references for the listbox, is there a way the VBA can be tweaked to sort them in ASC order regardless of which is the last/current role?

    Thank you, John!!

    Tom
    Attached Thumbnails Attached Thumbnails SortOrder.JPG  
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 5
    Last Post: 08-14-2019, 04:43 AM
  2. Dual listboxes -- need to modify process
    By skydivetom in forum Queries
    Replies: 3
    Last Post: 08-11-2019, 04:19 AM
  3. Replies: 20
    Last Post: 08-03-2019, 03:21 AM
  4. Query modification
    By endri81 in forum Queries
    Replies: 28
    Last Post: 02-29-2012, 09:50 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