I have inherited an Access database that was written in VBA and am still an amateur when it comes to VBA. I am tasked with computing a selection criteria based on a number of values that have to be banded or grouped. I have solved the grouping problem but would like assistance on the selection. Here is a sample of what I need to do:
Example:
Values: 37,36,35,33,33,32,31,30,30,25,24,22,22,21,20,17,15 ,10
Values are banded on a 4 integer grouping scale as follows starting with the top score then subtracting 3 from the top for the start of the range.
Band A: (34 - 37) Values: 37,36,35
Band B: (30 - 33) Values: 33,33,32,31,30,30
Band C: (22 - 25) Values: 25,24,22,22
Band D: (18 - 21) Values: 21,20
Band E: (14-17) Values: 17,15
Band F: (7 - 10) Values: 10
I have already written the VBA code to do the banding and updated my table. Now here is the tricky part: Each score is associated with a Level, either 17,15,12,7 or 4. I have a set number that I need to select by Level based on the Value score and band. Now, the Values cannot be selected more than one level above what they currently are, for example: a level 7 cannot be selected for a level 15.
Level 17 =3 selected
Level 15 = 4 selected
Level 12 = 7 selected
Level 7 = 3 selected
Level 4 = 0 selected
Example: If I need 3 to be selected for level 17. all 3 Values in Band A would be selected provided they were at level 15 or above.
Now, If I need 4 to be selected for level 15 and I have 6 values in Band B, I treat all scores equal which are not below Level 12. I then calculate a separate scoring and then choose the hightest from this separate score. The Top 4 would be selected based on this and the remaining 2 would be selected on the next Band C.
Now, If I need 7 selected for Level 12 out of Band C, I already have 2 from the previous and need an additional 5 selected. Since Band C has only 4 values, all are selected provided they are above a Level 7.
Note: Level 4 needs to be automatically default to a value if not selected for Level 7.
I simply keep going until all are selected. I would like to update my table with a Level Value if the score is selected.
Please Help! I thought I could do this in a set of queries but I believe this will require several loops.
My thoughts are that I would first loop through the scores for a level test, then compare the total count per band to the total of my selection, select those that did not meet the level test until I reach my selected number. Any thoughts?