Handicap/Grade design question

[IfButOnly]

I am having a problem coming up with a design for reporting by grades within a golf recording system. I want to calculate the grade for each player within a query, based on their handicap.

tPlayer contains the fields pName & pHandicap. tGrades currently contains gGradeName, gMaxHandicap.

In a query I want to be able to list by Grades
Grades could be
A , 10
B , 15
C , 27

or
A , 10
B , 14
C , 19
D , 32
or whatever. but the number, name and ranges of Grades may change.

While it may seem odd, I want to be able to change the Grades and requery on the same Players data.

I feel it should be simple but have not been able to come up with either the design or SQL that would enable me to do this cleanly.

Hope this makes sense - any help would be appreciated.

Many thanks...peter

 

[Brian1960]

Think Backwards

In the code build a recordset RS with this query

Select * from tGrades order by tGradeName desc

This will give you D,32 as the first record.

You need to add a field for grade to the player to make it simple; pGrade

Then run a commad to update the pgrade for the players and loop through your RS recodset running the command for each record in RS.

CommandText= "Update tPlayer set pGrade = '"& rs.fields("gGradeName") &"' where pHandicap < "& rs.fields("gMaxHandicap") &" "

Be carefull of field types and where to use ' or " .
After each loop through the RS the pGrade will be updated if it is less than the max allowed and left alone if greater than.

 

[IfButOnly]

Thanks for the reply.

I am trying to avoid storing the field pGrade as it is a calculated field which will, in this case, only be used for 'what if' reporting'. My thought had been to have some SQL statement as RecordSource to a report --
SELECT GradeName, Handicap, Grade from tPlayers LEFT JOIN .... tGrades ORDER BY Grade ....

except, of course, Grade would be an expression.

Any thoughts?

Thanks again, Peter

 

[Brian1960]

I think the code is probably beyond my 10 years experience!!!
If you can add a range for each Group so that you can link where Handicap between MinRange and MaxRange that will help!

SELECT tPlayers.Name, tPlayers.HCap, tGroups.group
FROM tPlayers, tGroups where tPlayers.HCap between tGroups.minrange and tGroups.maxrange;

 

[IfButOnly]

Thanks Brian,

I've decided on the following approach.

When I enter the application, load a global public array with the grades and then in the sql have Grades pass the handicap to a function and return the grade.

It should be efficient enough if I don't have to keep reloading the array.

Thanks a lot for you time and input.

Best regards .... Peter