Re: [MSAccessProfessionals] Winning team auto calculate

Hi Bill,

I tried to create a table named t_EventParticipants  in my sandbox database but there was already a table with that name 😉

Copy and paste this function into a new blank module and save the module with the name "modCalculations". Make sure you compile the code.

This function accepts the gteFscID, gteGteID, and optionally a "G" for greater or any other letter for less. It will return True for a winner, False for a loser, and Null if there are no records in the table matching gteFscID. You might get false winners if only one score has been entered so 

Public Function IsWinner(lngFscID As Long, lngGteID As Long, Optional strGL As String = "G") As Variant

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL As String

    Dim strSort As String

    If strGL = "G" Then

        strSort = "DESC"

     Else

        strSort = ""

    End If

    Set db = CurrentDb

    strSQL = "SELECT gteGteID FROM t_EventParticipants WHERE gteFscID = " & lngFscID & " ORDER BY gtePoints " & strSort

    'Debug.Print strSQL

    Set rs = db.OpenRecordset(strSQL)   'open recordset of participants in the game

   

    If rs.RecordCount > 1 Then 'more than one score recorded

        rs.MoveFirst

        IsWinner = rs(0) = lngGteID   'first record will be winner since the recordset is sorted by points

     Else

        IsWinner = Null  'either no participants or only one recorded

    End If

       

End Function

You can call this function in the after-update event of the score entries. You may need to save the record prior to running the function.

If IsWinner(gteFSCID, gteGteID) Then

       gteResID = 1   'winner

  Else

      gteResID = 2    'loser

End If

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Friday, March 5, 2021 1:17 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

Paul,

I like your idea and Duane agrees, which is always good.

I understand exactly what you are saying, which is good and bad.  It is good that I understand but it is bad that I am not so good at passing variables and writing functions.  I am good at tables, forms, queries, reports, but functions are difficult.  I will try to do some reading and figure out how to get started.  It is going to be painful.  Any tips you can provide would be greatly appreciated.

 

The first question is, where do I put the function?  Do I put it in the "After update" property of the score field, (gtePoints)?

 

I told you it would be painful.

 

Thanks,

 

Bill

 

Bill Singer

 

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy
Sent: Thursday, March 4, 2021 1:58 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

My first thought is a function to which you pass gteFscID.  It would open a recordset sorted by score, filtered by gteFscID.  Presuming there can be no ties,  the first record is the winner and the second record is the loser (presuming the sort is by score descending).  You can loop the recordset and assign those values.  I'd have code to make sure there are 2 records and both have valid scores entered in case the function is called before those conditions are met.

 

Paul

 

------ Original Message ------

From: "Bill Singer" <Bill.Singer@at-group.net>

To: MSAccessProfessionals@groups.io

Sent: 3/4/2021 11:45:54 AM

Subject: [MSAccessProfessionals] Winning team auto calculate

 

I have a sports tracking database.  I track game and practice schedules, scores, standing etc.  The database has been working pretty well for a few years with the exception of the human element, which is me. For each game I have to enter the score of each team and then indicate if each team was the winner or loser, by entering a 1 or 2.  Periodically I will enter the scores correctly by I will enter the results wrong by putting a 1 in place of a 2 or the other way around.  This creates grief for me an frustration for those coaches that see the records as incorrect.  I was wondering if I could get some help in trying to have the database calculate the winner or loser automatically.

I have a table that records the scores and game results.  Each game gets two records, one for the home team and one for the visitor team.  All the records are viewed on a continuous form.

The table is t_EventParticipants

The fields in the table are:

gteGteID – Key field

gteFscID – This is the ID for the Facility schedule, there one ID number per game, which would mean there two records with the same ID, one for Home one for Visitor.

gteTnaID – This is the Key field that referees to the team name

gteDesID – This is the key field that referees to the Home or Visitor designation

gtePoints – This is the field where the points are recorded.

gteResID – this is a Key field where a 1 or 2 is entered to designate winner or loser.

 

What I am hoping to do is to get the gteResID filed to automatically calculate but the problem is that I am comparing the scores in the gtePoints field from two records, which are the two records with the same gteFscID number one for the Home team and one for the Visitors team.    If it was comparing field in the same record I think I could figure it out.

 

Any help you could provide would be wonderful.

Bill Singer
Minnesota
Windows 10
Microsoft 365