Create Iif() Statement or Query to check multiple values in multiples rows matched on ID

Question

I am managing a student database. Each semester, we register students for their classes, and this progress is tracked in a table, [Registration Status Record]. Each row in the table consists basically of: [Student ID], [Semester], [Registration Status], and [Notification Status].

Example data:

• [Semester] (Semester in which the student is registered)
  • Fall 2016, Spring 2017
• Registration Status (Whether the student's registration is complete)
  • Complete, Needed, Error
• [Notification Status] (Once registration is complete, whether the student has been notified)
  • Notified, Pending

What I would like is to create another query or Iif() statement that will check all rows for any given student and return their overall registration status irrespective of semester.

If using an Iif() statement, here is some of the criteria I would be looking for:

• If a student is registered for all semesters successfully and they have been notified regarding each semester, it would report back something like "All Registrations Complete, Student Fully Notified."
• If a student is fully registered but notified of only one semester, "All Registrations Complete, Notification Incomplete."
• And so on... I think you get the idea.

As far as an Iif() statement, I have no idea where to begin. And as for query-building, I am equally stumped. I thought I would at least make one for students who are 100% done. Below is my feeble attempt, based upon another answer I found (https://dba.stackexchange.com/questions/1775/matching-single-column-against-multiple-values-without-self-joining-table-in-mys). The idea is that this query should show all fully completed students (100% registered and notified for all semesters):

SELECT [Registration Status Record].[Student ID], Count([Registration Status Record].[Registration Status]) AS [CountOfRegistration Status]
FROM [Registration Status Record]
WHERE ((([Registration Status Record].[Registration Status])="Complete") AND (([Registration Status Record].[Notification Status])="Notified"))
GROUP BY [Registration Status Record].[Student ID]
HAVING (((Count([Registration Status Record].[Registration Status]))>0));

My expectation is that if, for example, a student only has registration for Spring 2017 and it's 100% done, it will show them. If instead they have it for both Fall 2016 and Spring 2017 and it's 100% done, it will also show them. But if they have registration for both semesters and it's not fully complete for both, it won't show them. But it does! One of the students it returns, for example, is 100% done for Fall 2016, but only partially done for Spring 2017 (their registration is done, but they haven't been notified).

I know there must be some way to do this. I can articulate what I want in English or Spanish, but not SQL. Any help is appreciated... and apologies for the disjointed nature of this question.

Answer 1

Consider changing your HAVING clause, conditioning the IIF() value count to full count of Student ID instances:

SELECT t.[Student ID], Count(t.[Registration Status]) AS [CountOfRegistrationStatus]
FROM [Registration Status Record] t
GROUP BY t.[Student ID]
HAVING SUM(IIF(((t.[Registration Status])="Complete") 
           AND ((t.[Notification Status])="Notified"), 1, 0)) = Count(*)