I think you are going in the wrong direction. I don't see how using summary fields puts any significant limitations on how you sort your records.
I did have the feeling like I'm missing something. Thanks for the response.
Currently, doing summaries provides two hesitencies for me:
1. Time to load. Doing summaries would require 21 summary fields and 3 calculations on these summary fields.
For instance, we have Customer Surveys. To get a manager's employee average, I would need to have 2 summary fields and 1 calculation. I would have a summary for survey count, positive survey count and a calculation for good/count. Currently, the employee table has a customer survey average but just doing a summary of this does not factor in that not all employees have the same count of surveys.
I will test out how long it takes for these summaries to load but I have a feeling that it would take several minutes to display the layout.
2. Sorting capabilities. My current layout is using the employees table and has a subsummary that displays the manager metrics. If I sort by anything other than manager, it will only display a list of employees. This is what I meant on my limitations on sorting. If I wanted to sort by customer surveys, to see which manager's team had the most surveys, it would only show the employees. I can do a sort by manager then surveys but this sorts by manager name and then the survey count for that team. Would I need a subsummary part for all 21 of the summary fields?
Basically, these two reasons was why I was thinking of creating a manager table with it's own fields would be fastest and most sortable. Thanks again for the help. I really do appreciate it. If you can put me on the right path I would be much appreciative.
To get a manager's employee average, I would need to have 2 summary fields and 1 calculation.
Why? You don't need a "Total of" summary field, a "Count of" summary field and a calculation field to divide the total by the count. Why not use an Average type summary field and then you have one summary field and no calculation field needed to get that average.
From your description, I don't see why you would need 21 summary fields.
2. Open the sort records dialog box and take a look at the bottom of the dialog box. There's an option there that permits you to reorder your results by a summary field so you can group your records for the sub summary totals but also use the sub summary totals to determine the order in which those sub totals are listed.
Let me start by explaining why a summary of just the average would not work. Let's say that there are 5 employees on the team:
Employee Survey Count Positive Responses Average
1 5 4 90
2 10 6 60
3 5 5 100
4 7 7 100
5 6 4 66
So... if we did a summary-average of "Average", it would get 83.2. The correct answer would be the sum of "Positive Responses" divided by the sum of "Survey Count": 78.8.
The customer survey metric is the simplest. There are several other metrics that compose the 21 fields that I mentioned earlier.
Your #2 is a great tip. I have never used this but I expect to use it in the future. Thanks for this.
For my solution, I ended up creating a table of managers on startup and did all calculations in that table. It works flawlessly. There is the limitation of managers changing throughout the year but a quit and relaunch of the database will resolve.
Check out the "weighted average" option. I think that would produce the results you want.
AH-MAZING! That works out great! I'm going to work with it a bit but I think that may have been one of the things I was missing.