Getting rid of duplicate records in union query (1 Viewer)

terrytek · Apr 14, 2018

I have a union query for two unrelated tables:

Code:

SELECT PairID, "Pair" AS [Relationship],StudentID,AcademicYr
FROM tblPairAssignment
GROUP BY PairID, StudentID, AcademicYr
ORDER BY StudentID

UNION SELECT AssignmentID, "Class" AS [Relationship], StudentID, AcademicYr
FROM tblClassAssignment
GROUP BY AssignmentID, StudentID, AcademicYr
ORDER BY StudentID;

I need to find the number of unique StudentIDs per AcademicYr. Some students are in both a pair and a class in a given year, but I need to count each student only once per year. I can't just do a unique StudentID, though, because some students are enrolled in multiple years.

Can't quite figure out how to get the results I want.

Thanks!

CJ_London · Apr 14, 2018

assuming you have a unique student ID (i.e. it does not vary from year to year) just do a union query on the student id and academic year as a separate query. There is no need to group by as union queries will remove duplicates unless you use UNION SELECT DISTINCTROW.

you can then left join back to the tables if required to pick up pair/assignment ID's and relationship if required

The

Getting rid of duplicate records in union query (1 Viewer)

terrytek

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread