Removing data that are not applicable on a source table before to update a tracker

[JoaoVBTorres]

On my company, there's a training platform that is unoptimized and assigns all available trainings to everyone and generates a report. As the example below:

User	Training	Status
Staff A	Training 01	Assigned
Staff A	Training 02	Completed
Staff B	Training 01	In Progress
Staff B	Training 02	Assigned

The problem is, let's say Staff A works with finances and Staff B works with HR; let's also assume that Training 01 is related to HR, and training 02 is related to finances. In this scenario, Training 02 shouldn't even be assigned to Staff B, but it does and it also generates a pendency in the system. The issue is, we have hundreds of people and dozens of trainings.

To filter this mess, we have an excel tracker, which is manually updated (check the report to see status of applicable trainings, and manually update the tracker just like the Neanderthals did). The worst part is, we have to perform this task connected to a VPN. It takes a whole morning to do it.

My idea is to use this report as a source to update the tracker, but filtering out trainings that are not applicable. Any insights?

[Minty]

If you have a list of Trainings, I assume you also have a list of Departments.
What you need is another table DeptTraining where you store the Dept_ID and Training_ID as a junction table.

Once set up you then only load the trainings for the dept. concerned for the employee.

[JoaoVBTorres]

I see. My company works with clinical trials actually, so it's not a department, but a research center, a hospital/clinic.

We have a tracker for each center, but they have a diversified staff. Our idea is to update each center tracker, considering trainings applicable to each person.

EDIT: maybe it's a good idea to create a staff table and list all trainings as "applicable" and "not applicable", and then make a junction table with the report? How would I make the staff table status as not applicable "override" the report data?

[Minty]

In the reports query have criteria that says something like

WHERE TrainingStatus = "Not Applicable"

Then they simply won't show up.

I still think you would be better to not create records that don't apply.
You are storing redundant data, and then have to filter it out, which is wasteful of resources and complicates the processing.