0

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.

Community
  • 1
  • 1
  • Since you used `Count`, you've created an aggregate query. Since your only `GROUP BY` field is `StudentID`, you will get a record for any Student that has ANY record where the status is "Complete" and notification is "Notified". Try removing the count field, and the `HAVING` clause and see if that gives you the raw data you need. If you need the count, create another query based on this one that counts Registration Status. Post a comment about your findings, I can try to help when I get home. – MoondogsMaDawg Apr 18 '16 at 21:06
  • Thank you for your response. I tried removing the Count field, though it seems to be behaving similarly. For example, one student came up who is registered for Spring, but has "Error" for Fall. Here is what the revised query reads: `SELECT [Registration Status Record].[Student ID] 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];` I must leave for the evening now, but I will revisit this tomorrow. –  Apr 18 '16 at 21:35
  • Christopher, as you can see Parfait's solution below worked for my needs. All the same, thank you again for your help! –  Apr 19 '16 at 13:30

1 Answers1

0

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(*)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thank you Parfait. I will give this a try when I am back in tomorrow. –  Apr 18 '16 at 21:37
  • Parfait... c'est parfait! Pardon, pardon... really, though, this appears to work exactly as I would expect. The only issue that remains (and this may be better suited for a separate question) is figuring out how to account for missing semesters. E.g., a student may need registration for both Fall 2016 and Spring 2017, but only have a record for Fall 2016. Your query would list their ID, giving the illusion they are complete when they're not. Perhaps an additional query that uses some kind of helper table, or date-based system, might be helpful here... I'm not sure. Anyway, merci beaucoup! –  Apr 19 '16 at 13:29
  • Haha...thanks @Violet! Glad to help! I am not quite understanding the exception your raise though. The student with incomplete registration for any semester should not show in this query. – Parfait Apr 19 '16 at 19:51
  • Hahah, sure! Thank you again. And I will try to explain... this database I run only tracks status, not registration itself (that's the school-wide database, which is difficult to link to). So it's a manual task to ensure students have the needed rows for all applicable semesters. E.g., on the day registration opens for Fall 2017, I will have to manually append new rows with `Semester` as "Fall 2017"; if I don't, this query will still report everyone as being complete. I am brainstorming possibilities -- an append query, a date-based macro... I think it should be okay, but ideas are welcome! –  Apr 20 '16 at 17:13