I have a database that tracks graduate students’ academic employment. For instance, in 2014-15, Pam was a Reader and a Teaching Assistant (TA) in Fall quarter, a Graduate Student Researcher (GSR) in Winter quarter, and a Reader in Winter and Spring. In 2015-16, she was a TA in Fall, Winter, and Spring quarters, and she had an additional appointment as a GSR in fall.
Sometimes a student is appointed for two or three quarters at a time, and sometimes just quarter by quarter. Because of that wrinkle, I have the database calculating the number of quarters per appointment by using a Case calculation. (“Fall” = 1 quarter, “Winter¶Spring” = 2 quarters, “Fall¶Winter¶Spring” = 3 etc.). That value will only ever be 1, 2, or 3 because of the limited number of combinations within a given academic year.
Each separate hire, or appointment (i.e., Teaching Assistant in F/W/S 2015-16, or Reader in Fall 2014-15), is an individual record. The screenshot above shows Pam’s record in the Personnel table. The portal is pulling information from Pam's individual appointment records in the Appointments table. The screenshot below is an example of an individual appointment record (in this case, her Reader appointment for Winter and Spring 2014-15).
** My actual question: Each position has a limit on the number of quarters a student can be appointed to that role. So, I need to display the number of quarters in which Pam has served in each role.
For the Pam example above, I would like to display the following:
# of quarters as a TA: 4
# of quarters as a GSR: 2
# of quarters as a Reader: 3
I think what I basically need is a sum of the "# of quarters" field for each of Pam's records in which her Title = "Teaching Assistant," a sum of the # of quarters for each of her records where the Title = "GSR," etc.
The question is: How??