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.