I'll give that a try when I've had a break. My brain and eyes are fried from looking at data.
What makes the ACP example special, compared to the WHC consequence? Is it just because it comes first alphabetically and you don't really care which consequence gets chosen? Do you need the consequence listed at all?
I do need the consequence listed. the principals need to see the code to determine next steps, etc. I'll play around with what beverly said and give my consultant guy a call as well
One approach would be to use the GROUP BY clause to get distinct student IDs and then use the MIN or MAX function to get the first or last consequence for each student. For example:
SELECT fk_StudentID, MIN(EventType)
GROUP BY fk_StudentID