J
JS0001
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?
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?