Merge rows in Access or Excel with duplicate values?

Question

Hello all - I am trying to merge rows in Access or Excel with duplicate values in a particular column. I have merged multiple Excel files into one large file, but the issue is that I have a column titled "Student ID" that shows duplicate values. With a student body of close to 2,000 students, that's a lot of data to sift through. I would like to be able to merge duplicate rows in the "Student ID" column. The SQL command I used to merge the files was: SELECT * FROM [1] UNION SELECT * FROM [2] ect... However, the UNION does not merge duplicate values, and I was under the impression that it would. Please advise!

Answer 1

Sort the file by the student ID, then you'll be able to find the duplicates. If the rows are exact duplicates, you can delete them. If you have to add or concatenate data from rows that have the same ID, that will take a little more work.

Answer 2

a million. choose the selection of documents on your spreadsheet. 2. pass to the Ribbon and choose the concepts tab. 3. interior the concepts procedures area, click eliminate Duplicates. 4. interior the eliminate Duplicates communication container, verify the column(s) you like looked for duplicates. you ought to verify all columns that could desire to be a tournament to be seen a reproduction to circumvent the unintended removal of documents you desire to maintain. 5. click ok. A message will inform you the way many reproduction values have been got here upon and bumped off and how many unique values stay on your documents. If the end result isn't what you needed, hit Ctrl+Z to undo the removal of the duplicates and attempt returned.

Answer 3

UNION (as opposed to UNION ALL) will indeed eliminate dups but only for the entire row of a resultset. If I interpret your situation correctly, you want a result of 1 row per student with a column for each attribute indicating how many teachers checked it. One possible way:



SELECT a.studentID, COALESCE(motCount, 0) AS Motovation, COALESCE(achCount, 0) AS Achievement, ...

FROM (SELECT DISTINCT studentID FROM yourTable) a

LEFT OUTER JOIN

(SELECT studentID, COUNT(*) AS motCount FROM yourTable

WHERE checkedCriteria = 'Motivation' GROUP BY studentID) b ON a.studentID = b.studentID

LEFT OUTER JOIN

(SELECT...