Results 1 to 6 of 6
  1. #1
    All Thumbs is offline Novice
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    2

    Post Query to return only the Key values where joined values match multiple values in a another table.

    Context- I have experience building basic databases to capture, query and report mostly text based data. I have recently started a new role and inherited a smattering of (mostly abandoned) excel sheets. I am still laying out table design, but my question is based on the main purpose of the data it will contain.
    Scenario- I support multiple small teams working on various projects. Each project has training requirements that have to be met. Some requirements apply to multiple projects, some don't. It is common for team members to crossover to other projects to assist for periods of time.
    There will be a table for employee ID, Completed Training list , a reference table assigning ID's to each type of training, and I can either create a Projects table that holds the all project ID's and ID of each training required for each project , or create individual tables for each project that contain nothing but the project ID and the the list of required trainings.

    Question- My goal is to have a query return the ID's of all employees who meet (match) all of the requirements of a specific project. So , my query will use the the employee Id, Project(s), and completed training tables. How do I build this query? What alternate strategy might I use if this one isn't adequate?

    The other scenario is using an employee ID to see how which training they still need to access a project.( In the case of there being no matches).



    Any advice is appreciated! Cheers.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I vote against individual table for each project.

    The queries you describe can be complicated. Suggest you build a db schema and post it here for analysis.
    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
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I tend to agree with June --a table for each project is rarely a good design.
    I recommend you review "stump the model". A bit of testing of scenarios against the 1 table per project and the 1 project table will often clarify some issues and lead you to a design decision.

    Good luck. Let is know what you decide.

  4. #4
    All Thumbs is offline Novice
    Windows 11 Office 365
    Join Date
    May 2023
    Posts
    2
    Thank both for the prompt replies! I agree. I would like to keep the project info in a single table. There are two insights I could use. 1) What is the best way to indicate which courses are required in the projects list table so that the queries are as simply built and efficient as possible. 2) How to create a query that compares the courses all employees have completed to the courses required for a specific project and return their ID#s. The number of unique courses will grow every time we start a new project. A simple description of the relevant data per table would be as follows
    Table 1 - "Employees" - Emp #,Employee Name
    Table 2 - "Training List" - Training ID number, training description
    Table 3 - "Completed training" - Emp #, training ID #, Training expiry date
    Table 4 - "Projects" - Project ID#, Client, Training required

    I only add enough sample data to test with. Each of the table records will contain other data relevant to that record , but not relevant to this function/query. I am open to any input that helps accomplish the intended outcome. Table layout, and formatting included.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Again, queries you describe will be complex. I won't even try to construct without having data to test with. It's not difficult to attach file to post.

    Advise not to use spaces nor punctuation/special characters in naming convention. Better would be IDNum, TrainingRequired. Underscore is an exception but I avoid it as well just because I don't like typing it.
    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.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am attaching 2 files that may give some insight on tables and relationships. Recognize that these was designed for different rules so may not apply directly. My intent is to show some relationships that you may be able to abstract to your needs.

    Your initial question: My goal is to have a query return the ID's of all employees who meet (match) all of the requirements of a specific project. So , my query will use the the employee Id, Project(s), and completed training tables.

    For readers to give more focused answers, you will have to tell us more about Project and Requirements, and Training tables.
    Also what makes a specific Project?
    Attached Thumbnails Attached Thumbnails EmployeePositionTraining.jpg   EmployeeRoleCourse.PNG  

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

Similar Threads

  1. Replies: 44
    Last Post: 04-05-2021, 07:11 AM
  2. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  3. Change Table Index Values Based on query Values
    By thuzkee02 in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2015, 11:45 PM
  4. Replies: 2
    Last Post: 11-12-2012, 03:52 AM
  5. Replies: 0
    Last Post: 07-26-2011, 02:03 PM

Tags for this Thread

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