Share via

Microsoft Access - Qualified Employees

Anonymous
2022-09-06T19:20:08+00:00

I am brand new to Access. I posted this same question on an Excel forum a while back and was told by some long-time experts that I was pushing the boundaries of what Excel is designed for, and that I should instead look at access.

Here's some background information:

  • My company's warehouse has about 10 roles.
  • Each employee is assigned to multiple roles.
  • Each role has a list of topics that employees must be trained to.
  • Some managers have been assigning employees who are not properly qualified to roles that they shouldn't be.

Here are the tables I have in Access:

  • Employee Info - A list of employees with the roles that they are formally assigned to.
  • Training Matrix - A list of required trainings and which roles require them.
  • Training Log - A log of each instance of training conducted up to this point.

Management has asked me to regularly post a list of employees who are qualified for each role.

I need to figure out how to run a query (or other function) to review the training matrix to see required trainings, and then review the training log to see who has completed each training for a specific role. I'm prepared to organize my data in whatever way is necessary to get the job done.

Thank you!

Microsoft 365 and Office | Access | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,830 Reputation points Volunteer Moderator
    2022-09-06T21:53:50+00:00

    You need work on your table structure.

    The Employees info should ONLY have demographic info (EmpID, Names, etc.). The roles that are assigned to NEEDS to be a separate table (more on that later)

    A Roles table that describes each possible role an employee can be assigned to

    An EmpRoles table that lists each empployee and each assigned role. This is called a Junction table that models the many to many relationship that you have between employees and roles (each emp can have multiple roles and each role can be assigned to multiple emps). It would look like this:

    EmpRoles

    EmpRoleID (Primary Key autonumber)

    EmpID (Foreign Key)

    RoleID (FK)

    Now you also seem to have a many to many between Training and Roles (required trainings and which roles require them). Because you said roles (plural), it would seem a role can have multiple trainings. So, you would need 2 more tables here. A Trainings table and another junction table to line up the roles and their trainings.

    Once you revise your structure you should be able to create a query that shows whihc employees are qualified for any specific role.

    And yes, this is outside the capabilities of Excel.

    Was this answer helpful?

    0 comments
  2. George Hepworth 22,855 Reputation points Volunteer Moderator
    2022-09-06T20:52:21+00:00

    Check out the templates in Access. One of them, Students, might be a good starting place to learn more about good relational database table design.

    It's the most important aspect of making a usable Access tool is getting the tables right first.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-09-06T20:37:52+00:00

    Before working on queries, you first have to work on your table design. E.g. having employees and their roles in one table is realy bad design. Do some reading about relational database design.

    Was this answer helpful?

    0 comments