Use a loop in a query?

[jassy0928]

I am not quite sure how to begin to solve this issue so I will give a bit of an explanation.

I have a query that includes fields from T_employee and fields from T_courses, which is courses taught by each employee per semester and T_Additional, which is additional hours added to employees for each semester.

The query groups by employee and has a calculation of the teachers total hours per course based on teaching hours and factors for marking and preparation and adds the additional hours from T_additional.

Each teacher cannot have more than 44 hours, so there are additional calculations that calculate the consequence of subtracting one course from the total hours. The record that is closest to 44 without going over is optimal. In some cases, subtracting one course is not enough to get the hours below 44 and an additional course will need to be subtracted and perhaps a 3rd an so on.

I would like to evaluate which course is the most optimal to subtract and if it is not enough, then to loop through and subtract the next course until the hours are below 44, but as close to 44 as possible.

Can anyone give me direction on how I would go about this? Would a Do loop be what I need? And if so, where would I put it? I need to output the total number of hours that were subtracted and the number of courses.

I tried creating a query based on this one, grouping by employee and under the NetHours in the totals row, the max nethours <44. This works but there are still a few records where the nethours is >44. I would like to use a Where in the Totals row and in the Criteria somehow indicate that I want the largest number that is less than 44.

Thank you so much for any suggestions or feedback and please let me know if you need clarification.

Anita

 

[jassy0928]

Revised - IIf function in a calculated query

I have been working on this same problem and I am have a new approach.

Create a calculated field that is an IIF function:

IIF(Max([nethours]>44,[nethours]-(now here I want it to subtract the next min value in the hours to reassign field),max[nethours])

Can I refer to a record that way? It's second from the min? Or refer to the HrstoReassign field in the group to subtract from the Nethours?

Then I would like it to evaluate until it reaches the end of the records in the group. I assume there would be nested iifs but I don't know how to make it end and there is the scenario that even when all the courses are subtracted the SWF hours are still greater than 44 which is ok, I just want it to cycle through until each course has been subtracted and the SWF hours reach <=44.

Am I on the right track?

Thanks in advance,

Anita

 

[Uncle Gizmo]

Can you post a tabular view of the data returned from your query the data you examine to determine how to build the 44 hours.