MS Access - Combine Data from Multiple Records into Single Record

I have a SharePoint list which allows users from various teams across my organization to submit their level of impact for a given project. The list is configured so that each teams' level of impact is captured in their own individual column. Because of how I configured this, I have multiple rows in my data for the same project, with each containing only one impact value in the column for the team that submitted the record. I would like to combine all of the records for each project into one consolidated row that contains the impact from each teams' submission. For example:

**The data as it is collected and displayed from the SharePoint list: **

**Desired end result with merged records: **

Should this be done with a Access Query or a new table that joins the data somehow?

Thank you!

I have tried searching online for a solution, but most provide directions for concatenating values into a single column instead of merging multiple rows into one record.

This output should be possible with a simple aggregate query using Max() function.

SELECT ProjectID, ProjectName, Max(JanitorStaffImpact) AS MJSI,
Max(PaintingTeamImpact) AS MPTI, Max(ManagementTeamImpact) AS MMTI
FROM tablename
GROUP BY ProjectID, ProjectName;

As suggested in a comment, can also be done by a CROSSTAB.

TRANSFORM First(Nz([JanitorStaffImpact],Nz([PaintingTeamImpact],[ManagementTeamImpact]))) AS D
SELECT ProjectID, ProjectName
FROM tablename
GROUP BY ProjectID, ProjectName
PIVOT Sub & " Impact" IN("Janitor Staff Impact", "Painting Team Impact", "Management Team Impact";

A third approach suggested in another answer.