Best two of three scores

[Bhoc]

Have just about finished my large database for golf tournament. Am absolutely stuck on this one.
Part of the tournament is team events and part of those teams are teams of 3
The players in these teams play individually throughout the tournament and therefore score individually. Over the first two days their gross and nett scores are tallied.
to define the winners of these events only the best (in golf the lowest) 2 of the three scores are counted. I can work out the query for placing persons in their teams and also for their scores but cannot work out how to define the best of 2 of three scores from the team.
I have attached both the players table and queries re the teams to this post.
The players table includes numerous fields that are used for the whole database but the fields that relate to the teams of 3 only are
PlayerID
Fullname
clubteam
Team number scratch 36
Team number Nett 36
Nett 36
Gross 36

The first 3 fields are self explanatory
Team number scratch 36 refers to the number of the team this player is part of for her club eg Team 1 for XYZ club etc
Team number Nett 36 is the same but refers to a different competition than scratch
Nett 36 is the total score for the 36 holes less their handicap
Gross 36 is the total score for the 36 holes

The two competitions or trophies that I am trying to work out above are
Nett 36 Best 2 of 3 scores (lowest numbers) combined from each team will win with the lowest score
Gross 36 Best 2 of 3 scores (lowest numbers) combined from each team will win with the lowest score

I hope this makes sense.

 

[JHB]

Could you from the data you have in the database show, how you want the result?

 

[Bhoc]

JHB

The format doesn't really matter
I don't think I have numbered enough teams in the sample I sent but each teamclub as listed can enter as many teams of 3 as they have players, for example 24 players they can have 8 teams of 3. Each team is numbered 1, 2, 3 etc under each club
Example
Every player has a teamclub and every player can be part of a team within that club

Bega Club
Team 1
Person 1
Person 2
Person 3
Team 2
Person 4
Person 5
Person 6

Mollymook Club
Team 1
Person 7
Person 8
Person 9
Team 2
Person 10
Person 11
Person 12

and so on for each club and each team

But need to show:

Team, Names, scores
then what team came first, second and third

Example

XYZ Team Number 2
Mary Jones Score 123
Jenny Smith Score 114
Betty Brown Score 121
Total for this team (best 2 only) 235
ABC Team Number 3
Sandy Smith Score 115
Stephanie White Score 118
Colleen Black Score 124
Total for this team (best 2 only) 233


and so on for all teams - could be upwards of 20 or more

The final part is to show then which team came 1st, 2nd and third
1st is lowest combined score, 2nd is 2nd lowest and so on

Hope this makes sense to you

 

[JHB]

The best I can come up with in the moment, with the data in the database is to build an user function, to return the best total for each team.
The output is shown in the picture. The database with the function is attached.

 

[Bhoc]

JHB

Thanks worked great and I was able to make up the same for the Nett 36 holes
Only has a problem when one of the team members does not have a score next to their name.
User needs to ensure all scores are entered before this is used

 

[JHB]

JHB
Only has a problem when one of the team members does not have a score next to their name.
User needs to ensure all scores are entered before this is used

Then put in a criteria that omit the null values.

  Dim dbs As Database, rst As Recordset, Total As Long
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT TOP 2 clubteam, [Team Number Scratch 36], [Gross 36 holes] " _
  & "FROM Players " _
  & "WHERE ClubTeam=" & "'" & ClubTeam & "'" & " And [Team Number Scratch 36]=" & TeamNumber & "  AND Not [Gross 36 holes] Is Null " _
  & "ORDER BY clubteam, [Team Number Scratch 36], [Gross 36 holes]")
  If Not rst.EOF Then
    Do
      Total = Total + rst![Gross 36 holes]
      rst.MoveNext
    Loop Until rst.EOF
  End If
  TeamTotalGross = Total

 

[Bhoc]

JHB

sorry never got back to you on last post - exactly what I needed thanks