Query: Calculate Total Per Record and Finds Highest

[demzre]

I am an Access novice who is stuck and was hoping one of you experts can help.

I am building a database to manage a bowling league. I am trying to create a query that determines the winning team. Here are the tables involved and related fields in brackets:

TEAM (TeamID, Name)
PLAYER (PlayerID, TeamID)
GAME (GameID, PlayerID, Game, Score)

TEAM has a 1-to-many relationship to PLAYER via TeamID and PLAYER has a 1-to-many relationship to GAME via PlayerID.

What I want to do is to create a query which determines the total score for "each" team. In addition, a column in the query should return a message "Win" or "Loss", depending on Team who has the highest total score.

Example: Team A's total score 108, Team B's total score 210, Team C's total score 150. Team B would be a "Win" while Team A and C will be a "Loss".

I feel like I'm so close to getting this to work. Help if you can. Any tips will greatly be appreciated.

Thanks,
Albert

 

[MichaelRed]

see thread705-590307 and look for the routine "basMaxVal" for the "Highrst" score.

For the total score, the following should be useful.

Public Function basRowSum(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 4/9/2002
    'To return the AVERAGE or a series of values

    'Sample Usage:
    '? basAvgVal(1,12,3,5,78)
    ' 99

    '? basAvgVal(9, 1, 5, 3, 13.663)
    ' 31.663

    Print basRowSum(9, 1, 5, 3, Null, 13.663)
    ' 31.663

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim MyAccum As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
    End If

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) <> &quot;&quot; And Not IsNull(varMyVals(Idx))) Then
            MyAccum = MyAccum + varMyVals(Idx)
            Jdx = Jdx + 1
        End If
    Next Idx

    basRowSum = MyAccum

End Function

For the overall design, mayhap others would like to contribute.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[demzre]

To MichaelRed:

Thanks, I'll give your suggestion a try.