Calculated field - Times and scoring (1 Viewer)

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
Afternoon everyone

Im pretty new to this so please go lightly on me.

I have a table:

name, swimtime, score

john 18:00
smith 22:00


I m looking to score swimmers based on the time they swam a race - Example such as scoring 50 points if they finished in under 20 minutes - I thought a calculated field could achieve it but cant work out, nor find online anywhere how to score it.

I have been trying to do this: if([Swim] <= & 00:20:00, [50])

Any help would be hugely appreciated

Thank you in advance
 

sneuberg

AWF VIP
Local time
Today, 06:44
Joined
Oct 17, 2014
Messages
3,506
One way to do this is have a public function in a module that returns the scores. Something like:


Code:
Public Function GetScore(tm As Double) As Double

If tm < 10 Then
    GetScore = 20
ElseIf tm < 20 Then
    GetScore = 30
ElseIf tme < 30 Then
    GetScore = 40
Else
    GetScore = 50
End If

End Function

Then to use it in a query just put:

Score: GetScore(([Swim])
 

plog

Banishment Pending
Local time
Today, 07:44
Joined
May 11, 2011
Messages
10,298
You would set up a points table with 3 fields:

Points
LowMinutes, HighMinutes, Points
0:00, 20:00, 50
20:00, 25:00, 25
25:00, 30:00, 15
etc.

Then when you want to determine points you create a query. Unfortunately the Query Design Tool doesn't support creating the type of JOIN you want so you will have to dive (like what I did there?) into the SQL and create the JOIN manually. It will look something like this:


Code:
...LEFT JOIN Points ON YourTableNameHere.swimtime>=Points.LowMinutes AND YourTableNameHere.swimtime<Points.HighMinutes
 

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
Thank you for the reply - Appreciated

I tried this but get an error when adding the expression: GetScore(([Swim])) tried using: score: GetScore(([Swim])) but same issue

Any thoughts on what I maybe doing wrong?
 

sneuberg

AWF VIP
Local time
Today, 06:44
Joined
Oct 17, 2014
Messages
3,506
Sorry I had a typo in that function. Here it is again.


Code:
Public Function GetScore(tm As Double) As Double

If tm < 10 Then
    GetScore = 20
ElseIf tm < 20 Then
    GetScore = 30
ElseIf tm < 30 Then
    GetScore = 40
Else
    GetScore = 50
End If


End Function

Also the database is attached where I tested it (this time).
 

Attachments

  • SwimScores.accdb
    420 KB · Views: 21

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
Thank you very much.

Just to throw a spanner in the mix, if I add more sports (hurdles) I would use the same technique to produce a score - But how would I add scores from all sports together, use a DSUM?

Thank you in advance
 

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
I have just changed the module to reflect the times I need but it shows an error as soon as I change it

The swimming times in my table are in this format 22:00 to represent 22 minutes, as soon as I do: If time < 18:00 Then - It goes straight to a compile error

Any ideas?
 

sneuberg

AWF VIP
Local time
Today, 06:44
Joined
Oct 17, 2014
Messages
3,506
I suggest representing the times as numbers in the table e.g. 20.0 minutes, and the use the format function to display them with a colon.
 

MarkK

Super Moderator
Local time
Today, 05:44
Joined
Mar 17, 2004
Messages
7,761
I think plog's approach--doing a join to table values--will be considerably faster for large sets of data. But if you only call that VBA function for a small number of rows, you may not notice a delay.
 

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
I think plog's approach--doing a join to table values--will be considerably faster for large sets of data. But if you only call that VBA function for a small number of rows, you may not notice a delay.

Thanks for the reply,

Where exactly would the JOIN go within the SQL? So far I have:

SELECT tblSourceData.Athlete AS Athlete, Min(tblSourceData.Swim) AS MinOfSwim, Min(tblSourceData.Bike) AS MinOfBike, Min(tblSourceData.Run) AS MinOfRun, Count(tblSourceData.Athlete) AS EventsCompleted, Round((100*Count(tblSourceData.Athlete)/(SELECT SUM(b.eventsCompleted) FROM (SELECT COUNT(tblSourceData.Athlete) AS EventsCompleted FROM tblSourceData
WHERE tblSourceData.Gender="f"
GROUP BY tblSourceData.Athlete
HAVING Count(tblSourceData.Athlete)>=5
) AS b)),2) AS percentage
FROM tblSourceData
WHERE (((tblSourceData.Gender)="f"))
GROUP BY tblSourceData.Athlete
HAVING (((Count(tblSourceData.Athlete))>=5));
 

MarkK

Super Moderator
Local time
Today, 05:44
Joined
Mar 17, 2004
Messages
7,761
Take a look at plog's SQL. You'll need an additional table that stores the thresholds and points and so on.

But it looks like you have a bad normalization problem too if you are subquerying the same table to get the athlete, her sex, her event count, AND the result times of her races. You might want to make your life easier, back up and get that sorted out first.
 

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
I cant really change my raw data, I have about 20,000 records for people in the races.

Some names are in there 50 times as they have completed so many

I tried to follow plogs SQL and already created another table with thresholds and points if the time falls between those times. But I am unsure where to put plogs SQL within my own code.

This is all still pretty new to me to be fair
 

MarkK

Super Moderator
Local time
Today, 05:44
Joined
Mar 17, 2004
Messages
7,761
I cant really change my raw data
Why not?

Writing queries is hard. Writing queries against a set of tables that don't accurately represent the real-world problem is torture. If this is new for you, I strongly recommend you do a little reading about Database Normalization, and gain some understanding of how to represent real-world objects using database tables.

Hope this helps,
 

sneuberg

AWF VIP
Local time
Today, 06:44
Joined
Oct 17, 2014
Messages
3,506
The swimming times in my table are in this format 22:00 to represent 22 minutes, as soon as I do: If time < 18:00 Then - It goes straight to a compile error

What type is that? Short text? Does the number after the colon represent seconds, i.e., 1:20 would be 80 seconds?
 

sneuberg

AWF VIP
Local time
Today, 06:44
Joined
Oct 17, 2014
Messages
3,506
I've modified the function in the attached database to accommodate a string input. It uses a another function to convert the time format of mm:ss to a double which is equal to the number of seconds. So the cutoffs are in seconds.

I don't understand how your existing database could be working properly with this format for time. Strings are sorted by the ASCII values of their characters so a string representation of a number doesn't alway sort correctly. Please look at the qryMin in the attached database. It selected the minimum time in the table. The result should be 5:30 but instead it is 10:10. I grant you that this could be fixed by changing 5:30 to 05:30 but I think it would be better not to rely on the data being entered like that. I suggest convert these times to a numeric type.
 

Attachments

  • SwimScores.accdb
    400 KB · Views: 22

bouncingtigers

Registered User.
Local time
Today, 05:44
Joined
Aug 9, 2016
Messages
18
Thank you very much for the updated one.

Ive amended the module but for some reason everyone is scoring 50 points. My criteria for scoring is below:

If tm < 1098 Then
GetScore = 50
ElseIf tm < 1140 Then
GetScore = 40
ElseIf tm < 1200 Then
GetScore = 30
Else
GetScore = 0
End If


I must be missing something trivial as everything looks the same as onn the copy you send me.

Thanks in advance
 

sneuberg

AWF VIP
Local time
Today, 06:44
Joined
Oct 17, 2014
Messages
3,506
I've modified the code in the attached database to your cutoffs. Which are as in the comments I added.

Code:
If tm < 1098 Then   'less then 18:18
    GetScore = 50
ElseIf tm < 1140 Then   'less than 19:00
    GetScore = 40
ElseIf tm < 1200 Then   'less then 20:00
    GetScore = 30
Else
    GetScore = 0
End If

If you run qrySwimScores you can see that it is scoring correctly. What are the times in your database? Do they all happen to be less than 18:18

Could you upload your database?
 

Attachments

  • SwimScores.accdb
    392 KB · Views: 16

Users who are viewing this thread

Top Bottom