Results 1 to 9 of 9
  1. #1
    Didgeridoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4

    Three tables with many-to-many relationships. Subform within a subform?

    I have 3 tables - Role, Task, Skill.


    Each Role can perform multiple tasks and each task requires multiple skills.
    I want to make a form which shows each task that each a role performs and also, which skills are required for these tasks.

  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
    Forms can be nested, up to 7 levels I think.

    What exactly is the issue?
    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
    Didgeridoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4
    I have successfully nested the task subform into the role form, so when I scroll through Roles and list of relevant tasks is displayed. I just can't then get the list of skills required for those task to be entered to. I have tried adding another subform but that just lists all skills and not only those relevant to the task selected.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sounds like data structure is incomplete - lacking junction table(s). If you want to provide db for analysis, following instructions at bottom of my post.
    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.

  5. #5
    Didgeridoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4
    I have created junction tables and made a query but I don't know how to create a form that will show each task and skill (without duplicates) for each role.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You don't want to use that query for forms.

    Usually, a form does entry/edit for only 1 table.
    There are no forms in db. Build form/subform(s) and use master/child links properties to synchronize related records.

    To build a standardized set of Roles related to tasks:
    Main form for Role and subform for Role_Task_Jnctn with combobox for selecting Task

    To build a standardized set of Tasks related to skills:
    Main form for Task and subform for Task_Skill_Jnctn with combobox for selecting Skill

    Either pre-populate Role, Task, Skill tables or use combobox NotInList event to add records 'on-the-fly' during data entry.




    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.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Didgeridoo-davegri-v01.zip

    Have a look at this. I changed the name of your keys and added autonumber prime keys where appropriate. Your query is not used.
    Added 3 forms, a main form with a tab control. Each tab holds a junction table subform.
    Quick and dirty to give you an idea how to elaborate/proceed.

  8. #8
    Didgeridoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4
    That is a huge help. Thank you so much

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    That is a huge help. Thank you so much
    Glad it was helpful, and thanks for the star.

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

Similar Threads

  1. Replies: 10
    Last Post: 04-25-2014, 12:53 PM
  2. Replies: 2
    Last Post: 09-03-2013, 11:06 PM
  3. Replies: 2
    Last Post: 03-15-2013, 10:46 PM
  4. Replies: 5
    Last Post: 11-25-2009, 09:13 AM
  5. Subform in a Subform and relationships
    By St3ph3n in forum Database Design
    Replies: 3
    Last Post: 12-06-2005, 06:34 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