Question Help with Golf database

[Rusty4me]

Hi, my name is Rusty
As the club captain, I have put myself right into with creating a database for a golf competition. I am not an expert but do know enough to create smaller databases with Access.
However, I am having problems (gone from gray to white) with trying to create a table that can be looked up in query that will give me an answer to what the stroke allowance for par is on each hole depending on the players Handicap & the Index of each hole. I did create an table with Handicap (foreign key) , Index (Foreign Key) and (StrokeAllowance - being the extra strokes to have Par) but using Dlookup to give me the answer didn't work.
I could do it easily in excel, but now need I need to do it in Access. (I do work so most times can only look at this forum at night)
Would muchly appreciate any ideas and solutions - or have I taken on a monster.

 

[John Big Booty]

Welcome to the forum.

Perhaps one of the Data Models here will give you some ideas (specifically #23).

 

[DavidAtWork]

If JBB's suggestion didn't prove suitable, then all you need is a simple table of the holes with fields for the hole number, par score and stroke index. You would then use Dlookup to return the par and stroke index and do a simple calculation based on the players handicap will tell you how many stroke(s) a player will receive on a certain hole and if you know his gross score you can calculate his net score or changing it slightly, his stableford score
David

 

[Rusty4me]

If JBB's suggestion didn't prove suitable, then all you need is a simple table of the holes with fields for the hole number, par score and stroke index. You would then use Dlookup to return the par and stroke index and do a simple calculation based on the players handicap will tell you how many stroke(s) a player will receive on a certain hole and if you know his gross score you can calculate his net score or changing it slightly, his stableford score
David

Thanks David - yes I have done that with the hole number, par score and stroke Index with the 5 different course we have and used DLookup to return the Par and Index. My problem now is the "simple calculation based on the players handicap". Could you give my an idea on what that calculation can be. Thanks

 

[DavidAtWork]

There are several ways you can do this dependent on at what point the net score or stableford is required. Personally I would write a small function call it calculateNetScore and paste this into a module. You will be passing three variables:

hole number,
either the current handicap or players database Id (in order to lookup his handicap)
and the gross score.

The function will return the net score for the hole

Function calculateNetScore(holeNumber as Integer, hCap as Integer, grossScore as Integer) as Integer

Dim strokeIndex, netScore as Integer
‘fetch the stroke index, replace SI, indexTable and holeID with correct table/field names
strokeIndex = Dlookup(“[SI]”,”[indexTable]”,”[HoleID] = ” & holeNumber)

Select Case hCap
Case 0 to 18
If strokeIndex > hCap Then
netScore = grossScore
Else
netScore = grossScore – 1
End If

Case 19 to 36
If strokeIndex > hCap - 18 Then
netScore = grossScore -1
Else
netScore = grossScore – 2
End If


Case >36
If strokeIndex > hCap -36 Then
netScore = grossScore -2
Else
netScore = grossScore – 3
End If


End Select

calculateNetScore = netScore


End Function





If you have a form which shows the relevant player and you are entering his gross score, you need to have 2 boxes per hole, one Hole1gross where you enter the gross score and a second Hole1net net alongside to capture the net score and you would use the OnExit event of Hole1gross and in that code you would apply some validation to check a valid number has been entered such as:
If IsNumeric(Me. Hole1gross) And Me.Hole1gross > 1 Then
Hole1net = calculateNetScore holeNumber, Int(hCap), Int(Me.Hole1gross)
Me.Hole2gross.SetFocus
Else
Msgbox “You have not entered a valid score…”,vbOK + vbCritical
Me.Hole1gross = “”
End If

Presumably you’d have sets of boxes for all 18 holes and this code would be applied to the OnExit event for each of the HoleXgross boxes
A similar function could be written to calculate stableford instead of net score, but in the function you need to take into account the Par on the whole as well, so that would need to be looked up the same as the stroke index is, but the calculation would be the same, get the net score and then use:
stableford = Par – netScore + 2
If stableford < 0 Then: stableford = 0
I hope all this makes sense, David

 

[Rusty4me]

Thanks David, it does start to make sense now. I try this when I get home