Calculating results based on a list sort
Here is the scenario.
I have a list of employees with a job title and a work center. I have an authorized headcount for each job title. I have a table of people with their job title and work center. I have another table that lists the job titles, the work center for each job title and the authorized headcount for each job title.
I am trying to create a list that will subtotal the existing employees so using a summary field in the people table, I can obtain the total number of employees for each job title.
This is where it gets tricky .... at least for me. I know I am missing something blatent here.
Problem 1: I don't know how to get the authorized headcount for the job title sub-summary bar. I don't even want to get started listing all the ways I have tried. I think I have it working with the people table and the job title table related by job title. I just don't know if that's the right way because I can't get through problem 2.
Problem 2: I want subtract the total number of actual employees subtotal from the authorized headcount for a job title in the sub-summary header. When I subtract the total authorized in a calculated field in the people table, it subtracts the total authorized heacount of all job titles from the actual count of the one in the sub-summary list. Messy to explain so I'm sorry if it doesn't make sense.
Problem 3: On the same list, I would also like to sort by work center and there again have a total authorized versus the actual headcount. But that would require coming up with a subtotal in the job title table? I don't know.
In the uploaded screen shot I changed the font color to white for the names and for the position just to be anonymous.