Calculation problems - searching content of a field in IF statement
In my database, I have a table for student info and a table for their registrations. There can be many registrations for one student. I have a layout in the Student table in which I have created a field that needs to show one value for the student (A for active, or R for Retired) depending on if the student has any active registrations, or none.
I have tried doing the calculation:
IF (RegistrationStatusFieldName = ''A''; ''A''; ''R'')
This shows a value of either A or R in the destination field, but it seems to arbitrarily choose ONE of the Registrations to look at instead of looking at all the Registrations for the given student and determining if any of them are A (then should show value A). If even one registration for that student is A, then the value shown should be A. If ALL the registrations are R, then the value shown should be R).
Sorry if my explanation isn't clear... Can anyone help please? How do I tell it to look at ALL the registrations for the student before determining the value?
Thank you SOOOOO much!