It’s time to write the first formula for my golf league program. I started with a function to compute the handicap because it was at the bottom of my sketch. I knew that this program was going to be primarily UDF’s that filled worksheets. Because of that, I need to be aware of speed throughout the development. I start by establishing an ADO connection to my database and keeping that connection live as long as the workbook is open. This is the contents of my MGlobals module:
Public Const sQRYPLYRWKSCR As String = “SELECT TblPlayers.Player, TblScores.WeekNum, “ & _
“[Hole1]+[Hole2]+[Hole3]+[Hole4]+[Hole5]+[Hole6]+[Hole7]+[Hole8]+[Hole9] AS WeekScore, “ & _
“TblScores.Team FROM TblPlayers INNER JOIN TblScores ON TblPlayers.Player = TblScores.Player”
Sub InitGlobals()
Dim sCon As String
sCon = “DSN=MS Access 97 Database;”
sCon = sCon & “DBQ=C:DickGolfLeagueTenthHole.mdb;”
sCon = sCon & “DefaultDir=C:DickGolfLeague;DriverId=281;”
sCon = sCon & “FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”
Set gadoCon = New ADODB.Connection
gadoCon.Open sCon
End Sub
This sub is called from the Workbook_Open event to get the connection opening overhead out of the way before the first function is even called.
The logic of the handicap formula is this: Every player has a starting handicap and it’s stored in TblPlayers. The handicap is the average of a player’s most recent three scores times 80%. If there aren’t three scores, the starting handicap is used as one of the scores. A player’s handicap, then, may be the average of his first round and his starting handicap, thus only two “rounds” would be used. The theory behind these rules is that your handicap will reflect how you’re playing now, rather than how you’ve played over a whole season. Here’s the function:
Optional ByVal lweek As Long = 0) As Long
Dim rsWeekScore As ADODB.Recordset
Dim sSql As String
Dim lRecordsProcessed As Long
Dim dTotalScore As Double
Dim dAvgScore As Double
Dim dAvgDiff As Double
Dim sWeekWhere As String
Const dSCALE As Double = 0.8 ‘only 80% of average scores are used
Const sWEEKSCORE As String = “WeekScore”
‘If a week is provided, a where clause will be added to the sql to
‘limit the records to only previous weeks
If lweek > 0 Then
sWeekWhere = ” AND TblScores.WeekNum < “ & lweek & ” “
End If
‘Open the connection if it’s not already
If gadoCon Is Nothing Then
InitGlobals
End If
Set rsWeekScore = New ADODB.Recordset
‘Limit the records to just the player and optionally the week
‘Records are ordered descending by week to use the most current
‘scores in the calculation
sSql = sQRYPLYRWKSCR
sSql = sSql & ” WHERE (((TblPlayers.Player)=” & lPlyr & “))” & sWeekWhere
sSql = sSql & “ORDER BY TblScores.WeekNum DESC;”
rsWeekScore.Open sSql, gadoCon
‘Add up the three most recent scores
Do While Not rsWeekScore.EOF And lRecordsProcessed < 3
lRecordsProcessed = lRecordsProcessed + 1
dTotalScore = dTotalScore + rsWeekScore.Fields(sWEEKSCORE)
rsWeekScore.MoveNext
Loop
‘If there aren’t three scores to add up, include the starting HC
If lRecordsProcessed < 3 Then
dAvgScore = (dTotalScore + GetStartingHC(lPlyr)) / (lRecordsProcessed + 1)
Else
dAvgScore = dTotalScore / lRecordsProcessed
End If
Handicap = CLng((dAvgScore – 36) * dSCALE)
End Function
I needed to have the optional week argument because I need to compute two different handicaps. The handicap through last week is the handicap you use for this weeks match play. The handicap through this week determines which golfer you play next week (The low handicap golfer from one two-man team plays the low handicap golfer from another).
Each time this function is called I’m creating a new recordset. So much for those speed considerations. I think what I should do is bring in the recordset to a global variable without any where clauses, then iterate through the records picking out what I need. I’m guessing that the additional overhead of looping through the records will be more than offset by savings from not creating a recordset. Time for a little testing.
Let me first explain that I’m not a golfer and know little about the game and that I have little experience of Access.
I look at the problem in terms of Excel.
I would set up the handicap table with a column for this week as “Week 0?, last week as “Week -1?, the week before as “Week -2? and the week before that as “Week -3?.
A new player would then have his handicap placed in Weeks -3 to -1 and this weeks handicap would be the average of the three weeks. X *3 /3 =X.
Once each week, I would rename the columns (or shift the names right one column) and then delete the first column, so each week a new handicap can be calculated in column “Week 0?.
That way, there’s no need to take any further account of a new player, as his starting handicap will be shifted off the table and will be deleted after 3 weeks.
As I said, no interest in golf (“A good walk spoiled”) and not much knowledge of Access, so feel free to disregard this if it’s of no help to you.
Henry
The description of golf I enjoy is
“… [the game of] putting a wee ball into a wee hole with tools ill designed for that very purpose.”
– Jon