CODEWITHSUNDEEP
ms-accessmergesharepoint-list
LDoan
LDoan

Reputation: 3

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: **

Data from SharePoint List

**Desired end result with merged records: **

End Result

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.

Upvotes: 0

Views: 275

Answers (2)

June7
June7

Reputation: 21370

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.

Upvotes: 1

Shahram Alemzadeh
Shahram Alemzadeh

Reputation: 1140

Can also be written using joined query :

SELECT A.ProjectID , A.ProjectName , A.TeamA , B.TeamB, C.TeamC  
FROM (
           (SELECT * FROM DATA WHERE Submitter="Team A") AS A
INNER JOIN (SELECT * FROM DATA WHERE Submitter="Team B") AS B ON A.ProjectID=B.ProjectID)
INNER JOIN (SELECT * FROM DATA WHERE Submitter="Team C") AS C ON A.ProjectID=C.ProjectID

Upvotes: 0

Related Questions