4 Replies Latest reply on Mar 29, 2016 1:04 PM by dtcgnet

# A counting problem

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.

Screenshot:

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??

• ###### 1. Re: A counting problem

Isn't this a summary field when sorted by Title? Since this is a summary of a calculated field (# of quarters) I think you would need to make it a stored field.

Just an aside, not sure what your % column is calculating.

• ###### 2. Re: A counting problem

Couple of things.

In Quarter, I see Fall, Winter, Spring, F/W/S, F/W/S (A.I.). What is A.I.?

Not knowing about that, I suggest that you create an auto-entered number field called something like, "NumberOfQuarters". Make it an auto-enter field, and use the formula ValueCount ( Quarter ). REMOVE the options which allow for F/W/S. If I click just Fall, NumberOfQuarters would be 1. Click Fall, Winter, and Spring, it would be 3. ValueCount would be very helpful for you, I think.

If you did that, then had a report in which you sorted by the "Appointment To" field, you'd be very nearly there. Just add a field called NumberOfQuarters_smry, of type summary. It would be the Total of NumberOfQuarters. On the report layout, you could add a subsummary part when sorted by Appointment To. Put the NumberOfQuarters_smry field into that part.

Go to the layout, sort, and all of the totals you're after would be calculated automatically. Give that a shot.

• ###### 3. Re: A counting problem

That helped a lot, dtcgnet.

The reason there was both a F/W/S option and a F/W/S (A.I.) option isn't very interesting -- it's based on the fact that the pay period and start date for fall+winter+spring appointments is different for Associate Instructors than it is for other people. Not worth going into detail about. I can change the pay and start date calculations elsewhere and make everything work out in the end.

Thanks!

• ###### 4. Re: A counting problem

In your subsummary report, create a total of two subsummary parts. One for when the found set is sorted by Appointment To and one when it's sorted by Employee and Appointment To. Add that summary field to each part.

When you sort by Appointment To (only), you'll see something like:

TA 3

GSR 7

Those will be totals for each category of Appointment To.

When you sort by Employee and Appointment To, you'll see:

John Doe 7

TA 1

GSR 3

Bill Smith 9 (the total for Bill Smith)