Extract 2 results from a 3 field query. (1 Viewer)

I have 3 fields (R1,R2,R3) containing scores and am trying to extract the best 2 scores. One of the major issues is that one or more of the 3 fields could contain a zero value.
R1 R2 R3 Result I get
80 75 0 80 75 155 75 0
85 81 79 79 81 160 81 79
87 84 84 84 84 168 84 84
0 76 75 76 75 151 0 0
79 84 81 79 81 160 79 79
I have been using the IIf statement comparing rounds 1+2 and then comparing that result with round 3. Works fine if scores from all rounds are different and when R3 is lower than the result from Rounds 1+2. If any round contains a zero it returns zero. Cannot make fields greater than zero because that excludes all the rounds

put this in a Standard Module:


the first parameter of function BestOfTwo is the ranking number, put 1 if you want to return the highest score, put 2 if you want to get the second hightest score.


on your query, should look similar to this:


SELECT R1, R2, R3, BestOfTwo(1,R1,R2,R3) As Best, BestOfTwo(2,R1,R2,R3) As 2ndBest From yourTableName;

Thanks for your help. I think I can understand what you are doing. The query that I originally used was actually the result of 3 queries. By making changes to the source table I have now been able to obtain the result from a new single query. This has resulted in having blank cells instead of zero values.
FirstOfDate Trophy PlayerID FirstOfHandicap Players SumOfR1 SumOfR2 SumOfR3
13-Jul-13 CQ 4 19 Gary Austin 90 86 86
06-Jul-13 CQ 7 16 Murray Baylis 91 84
06-Jul-13 CQ 9 18 Ian Behrnes 94 96
20-Jul-13 CQ 11 23 Martyn Bennett 104
13-Jul-13 CQ 28 17 Bob Calkin 93
13-Jul-13 CQ 29 24 Peter Campbell 99
13-Jul-13 CQ 30 31 Evan Carter 109 109 105
13-Jul-13 CQ 40 22 Blue Cook 90 92 92
20-Jul-13 CQ 44 36 Garry Cox 115 112
17-Apr-11 CQ 45 18 Ray Cox 99
13-Jul-13 CQ 50 15 Murray Davies 93 87 87
13-Jul-13 CQ 56 19 Jeff Domigan 91 81 85
13-Jul-13 CQ 57 6 Andrew Donald 86 77 80
13-Jul-13 CQ 64 19 Greg Fitzgerald 99
13-Jul-13 CQ 69 18 Calvin Gabites 95 92
13-Jul-13 CQ 70 18 Gary Gabites 93 98 94
13-Jul-13 CQ 74 32 Don Geddes 102 107
13-Jul-13 CQ 76 9 Paul Goddard 86 88 89
13-Jul-13 CQ 148 11 Russell Paul 89 87 86
This was the only way I could think of to get scores from 3 different days where the handicaps may have varied from round to round all in one line and getting rid of the zero values. Previously a player had to have a value for each round. I will attempt to make the changes to your Module and Expression to reflect my changes.
Cheers

Russp42 said:

The query that I originally used was actually the result of 3 queries. By making changes to the source table I have now been able to obtain the result from a new single query.

Click to expand...

I suspect you may have gone in the wrong direction by denormalizing when you changed the table. Tell us about how you are storing the raw data. You need to get this right or you will be requiring more clumsy workarounds throughout the project.

While custom functions might make the queries look tidy, it is far more efficient to use the database engine to do the work.

why do we sometimes need to denormalized

http://vb123.com.au/index.html?200604_gr_flex.htm

arnelgp said:

why do we sometimes need to denormalized

http://vb123.com.au/index.html?200604_gr_flex.htm

Click to expand...

There is a crucial difference between the situations described in that article and my concerns over what I suspect the OP has done with their table.

That article is about using dynamic denormalization to move data to and from normalized storage for presentation on a form in order to avoid the pitfalls of denormalized storage.

A developer who denormalizes the storage tables for presentation reasons or to simplify a query is almost invariably making a grave mistake.

The database is for a golf club and primarily deals in scores. The Club Championships are played over 3 rounds and each of these rounds is a competition in its own right and has its own result. These rounds are played on different dates and players handicaps may have changed from 1 round to the next. The championships are played in grades and players are graded on their handicaps from the 1st round. The end result I am looking for is a report which gives the scores for each round and a total giving the sum of the 2 lowest scores for each player.
My query which gives the base information has 3 tables.
Competition - Comp ID, Trophy (eg R1),Date
Single Score- Joined by CompID,PlayerID,Score,
Members - Joined by PlayerID,Name, Handicap,Gender.
Originally I did the sort on the Trophy field. The problem with that is that I had to do a query for each round to extract the scores (R1,R2,R3) I also had to put a zero score against a player who did not play a round. This created a problem with zero becoming the lowest score.
By adding 3 fields R1,R2,R3 to the Single score table and entering the data there I am able to the retrieve the correct scoring and leave blanks where the player did not play that round.

arnelgp said:

put this in a Standard Module:

Code:Copy to clipboard

Public Function BestOfTwo(ByVal Standing As Integer, p1, p2, p3) As Integer
    Dim Best As Integer
    Dim arrReturn As Variant
    Dim arrValue(1 To 3) As Integer
    p1 = Nz(p1, 0): p2 = Nz(p2, 0): p3 = Nz(p3, 0)
    arrValue(1) = p1: arrValue(2) = p2: arrValue(3) = p3
    ' sort the array
    arrReturn = ArrBubbleSort(arrValue)
    BestOfTwo = arrReturn(UBound(arrReturn) - Standing + 1)
End Function

Public Function ArrBubbleSort(arrVariant As Variant) As Variant
    Dim lLoop1 As Long
    Dim lLoop2 As Long
    Dim lTemp As Long
    For lLoop1 = UBound(arrVariant) To LBound(arrVariant) Step -1
      For lLoop2 = LBound(arrVariant) + 1 To lLoop1
        If arrVariant(lLoop2 - 1) > arrVariant(lLoop2) Then
          lTemp = arrVariant(lLoop2 - 1)
          arrVariant(lLoop2 - 1) = arrVariant(lLoop2)
          arrVariant(lLoop2) = lTemp
        End If
      Next lLoop2
    Next lLoop1
    ArrBubbleSort = arrVariant
End Function

the first parameter of function BestOfTwo is the ranking number, put 1 if you want to return the highest score, put 2 if you want to get the second hightest score.


on your query, should look similar to this:


SELECT R1, R2, R3, BestOfTwo(1,R1,R2,R3) As Best, BestOfTwo(2,R1,R2,R3) As 2ndBest From yourTableName;

Click to expand...

This was 1 of my 1st two Threads and you replied to both. I am in my mid 70s with no programming training and struggle with some of the coding and can't interpret it all. I have since posted a few more threads and managed to solve all my other problems so have revisited these 2 unsolved ones. Happy to say I managed to make some sense of it and have achieved the desired result. Thanks