Re: [MSAccessProfessionals] Winning team auto calculate

Bill, is there a reason that two separate records are needed per game? What if you had v___ fields for visitors and h____ fields for home, as part of a single "game record"?  Thus instead of having to loop through a recordset, you could just use a calculated field(s) that compares what is in v___score against what is in h____score. It would require a redesign of your data model but I think it would make things easier going forward. I hope this helps.

On Thu, Mar 4, 2021 at 2:45 PM Bill Singer <Bill.Singer@at-group.net> wrote:

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

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115881) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Winning team auto calculate

I agree with Paul's suggestion of a function. There are also sports in which a lower score is better. I typically have a Sport table with a field storing either 1 or -1 to identify which is the winner, lower or higher.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Paul Baldy <Pbaldy@gmail.com>

 

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>

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

_

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

[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

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115878) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] HIDE BY DEFAULT MSG

Sayyed,

It looks like you are moving backwards from some recommendations and still haven't provided the data type of OTID.

Dim strSQL as String

strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT = false  And  OTID = " & Forms!F_EmpOTHeader!OTID

Debug.Print strSQL

Currentdb.Execute strSQL, dbFailOnError

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-execute-method-dao

Database.Execute method (DAO) | Microsoft Docs Database.Execute method (DAO) 09/18/2015; 4 minutes to read; o; O; In this article. Applies to: Access 2013, Office 2013. Runs an action query or executes an SQL statement on the specified object. docs.microsoft.com

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of crystal (strive4peace) via groups.io <strive4peace2008=yahoo.com@groups.io>
Sent: Monday, March 1, 2021 7:53 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] HIDE BY DEFAULT MSG

 

also another error. In SQL, just use the fieldname, not fieldname.value

CheckRightOT.value-->
CheckRightOT

On 3/1/2021 7:49 AM, crystal (strive4peace) via groups.io wrote:

hello Sayyed,

>"remove by default msg"

do you mean the feedback when you run the code?

Use Currentdb.Execute SQL
or db.Execute WHERE db is CurrentDb and then released when the procedure is done


not DoCmd

~~~

this syntax looks wrong:
"DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID

perhaps this, if OTID is a number?
"DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false And OTID = " & Forms!F_EmpOTHeader!OTID

if OTID is text,  then this:
& "'" & Forms!F_EmpOTHeader!OTID & "'"

kind regards,
crystal

On 3/1/2021 12:06 AM, SAYYED ASHFAQUE HUSSAIN via groups.io wrote:

Hi,  I need to remove by default msg of my below code. Is this possible?    strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID    Thanks in advance.

Re: [MSAccessProfessionals] HIDE BY DEFAULT MSG

also another error. In SQL, just use the fieldname, not fieldname.value

CheckRightOT.value-->
CheckRightOT

On 3/1/2021 7:49 AM, crystal (strive4peace) via groups.io wrote:

hello Sayyed,

>"remove by default msg"

do you mean the feedback when you run the code?

Use Currentdb.Execute SQL
or db.Execute WHERE db is CurrentDb and then released when the procedure is done


not DoCmd

~~~

this syntax looks wrong:
"DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID

perhaps this, if OTID is a number?
"DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false And OTID = " & Forms!F_EmpOTHeader!OTID

if OTID is text,  then this:
& "'" & Forms!F_EmpOTHeader!OTID & "'"

kind regards,
crystal

On 3/1/2021 12:06 AM, SAYYED ASHFAQUE HUSSAIN via groups.io wrote:

Hi,  I need to remove by default msg of my below code. Is this possible?    strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID    Thanks in advance.

Re: [MSAccessProfessionals] HIDE BY DEFAULT MSG

hello Sayyed,

>"remove by default msg"

do you mean the feedback when you run the code?

Use Currentdb.Execute SQL
or db.Execute WHERE db is CurrentDb and then released when the procedure is done


not DoCmd

~~~

this syntax looks wrong:
"DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID

perhaps this, if OTID is a number?
"DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false And OTID = " & Forms!F_EmpOTHeader!OTID

if OTID is text,  then this:
& "'" & Forms!F_EmpOTHeader!OTID & "'"

kind regards,
crystal

On 3/1/2021 12:06 AM, SAYYED ASHFAQUE HUSSAIN via groups.io wrote:

Hi,  I need to remove by default msg of my below code. Is this possible?    strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID    Thanks in advance.