[Gene Haines]

Our company requires all employees to take Mandatory Annual Courses. There are 30 courses.

I have been tasked with streamlining the Class Schedule.

I created an access database that queries all 30 courses and the personnel that are due to take the course(s) within the next 45 days. The query labels are EmpBadge, EmpLname, EmpFname, Course Name and EmpSupername

I export to excel and then sort by course name and then by Supervisors name.
I then subtotal by Course Name and wind up with 30 course/record related subtotals.

I am limited to a Class/Course Size of 14 and authorized to pull a maximum of 5 personnel per supervisor per Class/Course.

Is there a way to create in a Macro or VBA that would evaluate each subtotal and determine the number of classes required based on a max of 14 per class, also look at the max number of 5 per supervisor and the randomly select and assign a class designation to each record. Can it loop back through again if there would be more than 5 per supervisor in a class and increase the number of classes? The designation can be "Class A', "Class B", "Class C", "Class D" etc etc. Obviously the number of designations to use per subtotal is dependent upon the number of classes per course. There would be an empty column in the file and the header would be Class and this is where the designation would go.

Hope I am making myself clear . Any thoughts would be appreciated.

Thank you

Gene Haines