Can this be done a faster way...

[andy49]

Problem:

I have

Tblgrades

Score (will only ever be 0 up to 240)
Grafef txt field




TblBoundaries
CalendarId (integer)
F1 integer score needed to get grade 1
F2 integer score needed to get grade 2
F3 integer score needed to get grade 3
F4 integer score needed to get grade 4
F5 integer score needed to get grade 5


Eg 19 15. 25. 51. 106. 180

What I need.....

In tblgrade

A table with 241 rows with score and corresponding grade.


all scores up to f1 to have a grade 0
All scores f1 up to f2 to have a grade of 1-, 1 or 1+ depending on which third the grade is in.

Similar for grade 2,3,4 and 5

From the above example:

0-14 will be 0
15-17 will be 1-
18-21 will be 1
22-24 will be 1+


25-33 will be 2-
34-42 will be 2
43-50 will be 2+

And so on

I’ve managed to write a rather complex routine in vba which does work but wondered if anyone could help with just a query which might do the job faster.

Thanks for taking a look.

[ranman256]

yes, create tblGrade with 241 rows.
in a query, join the tblGrade table to the tblScore table to get your actual grade conversion.
No VB was needed for this.

[andy49]

Thanks ranman

I don’t have laptop with me but will try later. Surely this will only work where score is either 15,25,51 and so on.

And how will that create the 2-, 2 or 2+.

[June7]

As ranman suggested, table with 241 rows with two fields: Score, Grade. Build query that joins the tables on the common Score fields. Pull Grade field into the query field grid.

Of course, this assumes scores are always whole number.

An expression using Switch function could return desired value; however, might be too long for use in query but could be used in VBA. Example:

Switch(Score<=14,"0", Score<=17,"1-", Score<=21,"1", Score<=24,"1+", Score<=33,"2-", Score<=42,"2", Score<=50,"2+", continue for rest of ranges)

But you mentioned in your OP a need for a table with 241 rows. If you were already aware of that requirement, what exactly is the issue?

[andy49]

Ok I believe I may have failed to get the problem across. Here goes.



Above shows the table. I need to fill column 2 (or create a query which has column 1 and column 2)

The values I need depend on the table below.



So Any row with a score of 9 or less needs to be filled with a zero or N

Any row with a score of 10-14 needs to be filled with a 1 and so on.

I'm happy to leave the - or + issue for now if I can find a fast route to get.....




Hope you can help

Forgive the excel - it was the easiest way to show the issue

[June7]

Still not clear. Only 241 records, do manual data entry. Get the lookup table setup then use it in query joining to table with student scores.

Also, the GradeF assignments are not the same as shown in first post.

[ssanfu]

You've changed the score range and the grade..... but see if this is close...

[andy49]

Looks great Steve.

However the table which you've called GradeRange only comes with whole value grades rather than the - + breakdown which I have to add in program.

Looks like VBA may be the only answer.

Thanks all for your help. I'll mark as solved

[June7]

I am more confused. Steve's approach does show - and + values in the query. I also do not understand why you feel VBA is the only answer. What happened to the idea of a table with 241 records for the score to grade conversion? This would be a lookup table which would join to a table with actual student scores to show the appropriate grade.

[andy49]

Hi June. I don’t actually have the full complete lookup table.

All I get is which score is a grade 1, which score is a 2 and so on in a separate table. This has to be used to complete the full lookup table including - and + “thirds”

Any help?

[June7]

Still don't understand. Exactly what are you trying to accomplish? If you needed a lookup table with only 241 records then could have just built it and entered values with manual input. Why effort to generate this dataset with code? If you don't want to do manual input, then yes, VBA is the alternative because the Switch expression I described is probably too long for a query. Steve's example shows only 50 records so make adjustments as needed to produce the 241 record dataset.

[ssanfu]

Looks like VBA may be the only answer.

Andy,

You did not provide all of the scores to grade conversion values, so I used what I could.

June's method is better than the function - no code involved. You kept mentioning VBA, so I wrote the function.

Here is a form with both methods in subforms.
In the table "GradeScoreLookUp" you should fill in/fix the actual Grades all of the way to 240.
In the table "GradeRange" you should fill in/fix the Grades for the min score to 240.

This is to show both options side by side.......