Difficult 'points' problem - allocate by rank? Dynamic?

[Sharky II]

Hi guys, hope someone can help - i'm stumped.

Got a scoreboard for (say,) 20 teams. There are about 10 events each team has to do. For each event the team gets a score/time. I have learnt how to sort these and then allocate them a 'rank' (1st, 2nd, 3rd etc) by creating queries. This was documented on the knowledgebase area of the ms site.

However i now need to allocate 100 points to the team that came first, 95 to the team that came 2nd, 90 for 3rd, etc - but this must be dynamic. So if 'Team D' come along and get the fastest time, they automatically get ranked 1st and get given 100 points, and the rest get shifted down a place.

Can anyone think how i may be able to do this? If possible it would be great to have this fully dynamic - the no of points for each event must also be added together to form a total team score for all teh events, so it can't really be an unbound field working this out.

Anyone? I'm lost

EDIT - perhaps it could work by pressing a button - when you do so it checks which team is first, which is second etc when pressing the button, then allocates the first person 100, 2nd 95 etc - it not dynamic. Either way i don't really know how i would do this! I'm not too bothed about it being automatic/dynamic.

Thanks!

 

[KenHigg]

What are the tables you have at this point?

ken

 

[Sharky II]

Hi

Tables are as they stand are attached in the pic. I haven't put the fields in there as i am unsure as to which fields should be where. I started building this but then stopped as i want it to be totally planned before i start!

It's pretty clumsy as it stands. I can't think of a better way to do it. I can post more details on the spec if you think the structure could be better. I think the back end will be pretty simple, it's the front end stuff that's hard. I should just tell them to do it in excel.

 

[KenHigg]

This pc that seems to be undefined is the scoring ratio. If you fix the number of teams to 20 and do 100 for 1st, 95 for 2nd, etc., will the last place team get zero points? If so, what if you only have 19 teams? Will the last place team get 5 points? What if you have 21 teams?...

???
ken

 

[Sharky II]

hi. i'm been given this scoring ratio to use so all i have to know is that it must be done!

there are definately 20 teams - they have been booked. next year (annual event) there will be 40 but seeing as i'm doing this for nothing (it's a charity event) i think we should leave that until next year to worry about.

the scoring scheme isn't the problem though is it?

 

[KenHigg]

the scoring scheme isn't the problem though is it?

Nah, just that the easy solution would be a bit un-scalable because of hard the hard coded algorithim...


Would this work:

tblTeams
team_reno, pk
name
...

tblMembers
member_recno, pk
team_id, fk
name
...

tblEvents
event_recno, pk
date
...

tblScores
score_recno, pk
event_id, fk
team_id, fk
score
...


???
ken

 

[Sharky II]

Hi ken

That is essentially what i have, other than the eventdetails is called 'scores'. I have set up what you've suggested. What's a bit clumsy is the forms system (ie - a team based approach or an 'events' based approach'?) - but i'll worry about that later.

The guys are more interested about the features of the scoreboard - the points system etc. They want to do as little work as possible (=more work for me). So it has to rank people and then sort then allocate how many points are given for that event. Once that is done that's the major functionality out of the way

I found out how to do the ranking and the sorting (from teh knowledgebase and from qrysmp97.exe... but the points?

Thanks guys

 

[The_Doc_Man]

In general, when dealing with rank-ordering, you can determine a team's ordinal position with a formula in a query, which is therefore dynamic by definition. (It can change each time you open the query if the underlying table had changed between the two open-times.)

In one column, put a "1 + DCount()" where you are counting teams with a lower score than the score of this team. This means the team with the highest score is rank 1, teams with lower scores will have higher rank-order numbers. Handling ties could be tricky, though. To do it right, you would also need to know the rules on how to break ties (if there are such rules) or you would have to count the number of ties and do point allocation differently for those cases, perhaps.

The idea is to determine the rank-order this way. Then, if you have a point-count requirement (1st gets 100, 2nd gets 95, etc) and a formula is not usable, you can statically store the points with a table that holds rank and points (two fields) and as many entries as the positions that you allow. You can use a DLookup to get the points from the rank-order. Look up DCount and DLookup in the help files if you are not familiar with them.

Now, a wrinkle that you didn't mention but I've seen it before. Let's say that your table only pays points to the first 20 positions but if you are lower than 20 points, you get zilch. If so, store the 20 paying positions and when you do the DLookup, use the NZ function to supply 0 whenever the lookup returns nil.

Alternatively, put an index on the lookup table's "rank" column, then OUTER JOIN the score to the points table and use NZ to provide 0 when the outer join returns a null field. You can look up JOIN (including OUTER JOIN) in the help files, too.

 

[KenHigg]

Not sure I'm totally on board here so let's take it a step at a time:

So in my post, in tblScores, for an event and team, in the score field, you put 1, for first place, 2 for second place, etc.

Is this how you see it working so far?

ken

 

[Sharky II]

i'm with you doc_man!

I'm using the code similar to " Ranking: (Select Count (*) from Employees Where [HireDate] < [Emp1].[HireDate]+1 " from the aforementioned/appropriate help directory in the knowledgebase. In the examples i've tried it seemed to be an involved procedure but it worked!

I have not thought of using a lookup to get the values off the other table. This would be pretty cool. You mentioned a formula though - yes, a formula could be used i guess, 1st is 100, then 2nd is 100-5, then each one goes down by 5. Am still not sure how i would implement that though?

I am off for a meeting with them now. I will be back in an hour or two!

Any more suggestions would be really appreciated!

Thanks!

 

[Sharky II]

Not sure I'm totally on board here so let's take it a step at a time:

So in my post, in tblScores, for an event and team, in the score field, you put 1, for first place, 2 for second place, etc.

Is this how you see it working so far?

ken

hi ken - not exactly. it should work out hte rankings itself. that's fine, and i should be able to do that (he says!)

what actually happens is you enter either a team score (lets say if the event is an IQ test) or a team TIME (lets say if the event is a race). For time, shortest wins (or if it's endurance, longest does!), for score, if you have the highest IQ score, then you go into first place and get 100 points, 2nd highest iq score, you get 2nd place and 95 points.

This is done on each event, for each of the 20 teams, then somehow this is all added up in some kind of leader/scoreboard, totalling everything?

I reckon this would be the best way to do it?

 

[KenHigg]

Looks like Doc may come up with something I didn't see - I hate for you to try and keep up with us both. If Doc's solution doesn't pan out (which I'm sure it will), repost - I'll try to follow your progress...

Ken

 

[The_Doc_Man]

If you have a formula situation, then the DCount can be used twice.

Once for the number of the rank-order based on the teams that beat you, where you add 1. (And by the way, I said this backwards before.)

Once for the number of teams that you beat (plus 1) times a positive number of points. (This time it is correct.)

Suppose that you aren't on a curve. I.e. linear point allocation.

Then your formula is the Dcount of teams you beat times ( 100 divided by the Dcount of teams).

So for 20 teams, and you are in first place, your rank order is 1 because no team beat you so it is 0 + 1. Your points would be based on the 19+1 teams you beat * ( 100/20 teams) = 20 * (100/20) = 100.

For 3rd place (untied) out of 20, you would get 90 points using this method, 'cause you beat 17 teams. 17+1 * (100/20) = 90.

If you are on a curve or have to interpolate for ties, this gets trickier by a bunch. Not impossible, but no longer quite so simple.

Phase II of this computation might be based on the case where ties are possible. So you would have TWO components for the points.

You would have full points for the number of teams you beat (no longer plus 1) and fractional points for the number of teams you tied (plus 1), and the fraction is determined by the number of points per (untied ranking) divided by the number of tied teams. Suppose you tied for 3rd with one other team out of 20. OK, that means 2 teams beat you.

So your rank is tied for (2+1) = 3rd. (You can argue that it is 3rd-4th, and you would be right, but it is trickier to program to show that. Not impossible, but tricky.)

Your points would be based on the number of teams you beat. Well, out of 20 teams, 2 beat you and 1 tied you. So you beat 16 other teams. If you had 3rd place outright, it would be 17+1 * (100/20) = 90 points, right?

But here you have a 2-way tie. So you take 17 (no longer + 1) * (100/20) = 85, then add in (100/20)*(number who tied/number+1). This is 5 * (1/2) = 2.5 so your points are 87.5

Sanity check: You would award points for 100, 95, 87.5, 87.5, 80, etc. etc. and the points would balance out correctly.

Do it for a 3-way tie for 4th place.

Ordinal = number who beat you + 1 = 3+1 = 4.
Points = number you beat * value = 14 * 100/20 = 70, plus point-value * (number who tied/number+1) = 5*(2/3) = 3.333, final score = 73.33

Sanity check: You would award points for 100, 95, 90, 73.33, 73.33, 73.33, 70, 65, ...

Ok, the actual ranking for this last case would be tie:4th-6th - derived from Str(1+number who beat you) & "-" & Str(1+number who beat you + number who tied you)

Or something like that.

 

[Sharky II]

wow, thanks for that doc! i'm gonna re-read that now i think!

the situation after the meeting is a bit strange. they actually want it in a more 'leaderboard'/spreadsheet format. i've attached a gif of the type of thing they want (screenshot.gif).

it's difficult for me as it suits more of s spreadsheet type of application, but that's difficult for me as:

- i don't know excel very well at all
- i want to use loads of access features
- i want to use forms and reports to display the information.

These guys don't want the information in seperate 'areas' they want to see one big table (query?) of all the teams and all the events. Another example
of this is given in screenshot2.gif. They want to enter the details and then for it to do the maths for them.

However i'm so used to getting stuff into third normal form etc i can't think how to do this properly.

Can anyone help?

Many thanks

 

[Sharky II]

forgot to add 2nd pic

obviously i could stick it ALL in one table (the teams table for example), but then wouldn't it be difficult to do the maths etc?

when i try to do it, it ends up being like the other image i attached (access_attempt.gif)

if anyone could suggest anything that would really help

 

[SBM]

Ranking ?

Doc Man, how do you DCount() where you are judging the current value against other values ? 1+DCount("[Field]","Table","?").

In general, when dealing with rank-ordering, you can determine a team's ordinal position with a formula in a query, which is therefore dynamic by definition. (It can change each time you open the query if the underlying table had changed between the two open-times.)

In one column, put a "1 + DCount()" where you are counting teams with a lower score than the score of this team. This means the team with the highest score is rank 1, teams with lower scores will have higher rank-order numbers. Handling ties could be tricky, though. To do it right, you would also need to know the rules on how to break ties (if there are such rules) or you would have to count the number of ties and do point allocation differently for those cases, perhaps.

The idea is to determine the rank-order this way. Then, if you have a point-count requirement (1st gets 100, 2nd gets 95, etc) and a formula is not usable, you can statically store the points with a table that holds rank and points (two fields) and as many entries as the positions that you allow. You can use a DLookup to get the points from the rank-order. Look up DCount and DLookup in the help files if you are not familiar with them.

Now, a wrinkle that you didn't mention but I've seen it before. Let's say that your table only pays points to the first 20 positions but if you are lower than 20 points, you get zilch. If so, store the 20 paying positions and when you do the DLookup, use the NZ function to supply 0 whenever the lookup returns nil.

Alternatively, put an index on the lookup table's "rank" column, then OUTER JOIN the score to the points table and use NZ to provide 0 when the outer join returns a null field. You can look up JOIN (including OUTER JOIN) in the help files, too.

 

[The_Doc_Man]

You'll have to supply the exact field name criteria, but the idea is to use DCount to compare scores. Since a score is usually numeric, you have the easy case syntactically.

For a hypothetical setup in which you have a table of teams in which the team's cumulative score is in a field,

1 + DCount( "tblTeams","[AggregateScore]","[AggregateScore] > " & Str([AggregateScore]) & "AND [TeamID] <> " & Str([TeamID]) )

Or something like this. The idea is that you don't count your own score in this list but you count the other scores that are higher than yours, then add 1. So if NO scores are higher than yours, your rank order is 1.

Technically, you could leave out the team-id test since your own score is not greater than your own score, but I was being a bit pedantic for this one.

The point count discussed earlier uses the same exact DCount but with the < sign rather than the > sign. And the TIE count uses = instead of > or <.

Clearer?

 

[SBM]

Thanks for the reply. In simple terms what I am trying to do is apply a ranking to a forecast (sorted in descending order) for a given Product in a table. For example...

In Table SRated there are Two fields Called ProdCode and FCast. I'm trying to run a query on this table which will sort the Fcast into descending order and also show a ranking ...

ProdCode FCast Ranking
1234 900 1
5678 750 2
9012 700 3
....etc


SBM

 

[meboz]

Hi there,

I had a bit of a read of this situation and it seems to be a nice challenge.

I work for schools and have had similar 'normalization' issues with markheets that include all of the assessment tasks given.

What you want is to add the events before the competition, there can be 1 or many, and then at the end have a score sheet which presents in a denormalized format.

Well... I read a nice book on SQL and in the correlated queries section it gave a nice query which effectively denormalizes normalized data.

I tried to make the query a little more generic with VBA cos I could see a lot of applications of this query. Heres the code I used in my database.

Private Sub SetQuery(strConnection As String, strSQL As String)
        'set the query from which the merge document will pull its info
        Dim qdfNewQueryDef As QueryDef
        Set qdfNewQueryDef = CurrentDb.QueryDefs(strConnection)
        qdfNewQueryDef.SQL = strSQL
        qdfNewQueryDef.Close
        RefreshDatabaseWindow
End Sub

Private Sub cmdCreate_Click()

Dim strSQL As String

strSQL = "SELECT DISTINCT tblDueDiligenceRecord.LetterID, " & _
         "tblDueDiligenceRecord.StudentID, tblDueDiligenceRecord.KlaID, " & _
         "tblDueDiligenceRecord.LetterDate"

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT tblcriterialist.* FROM tblcriterialist;")
rst.MoveLast
rst.MoveFirst


Do While Not rst.EOF
strSQL = strSQL & ",(SELECT  tblLetterDetails.CriteriaID FROM tblLetterDetails " & _
         "WHERE criteriaid = " & rst.Fields(0) & _
         "AND tblLetterDetails.letterid = tblDueDiligenceRecord.LetterID) AS [Criteria " & rst.Fields(0) & "]"
rst.MoveNext
Loop

strSQL = strSQL & " FROM tblDueDiligenceRecord LEFT JOIN tblLetterDetails ON tblDueDiligenceRecord.LetterID=tblLetterDetails.LetterID;"

Call SetQuery("denormalize", strSQL)
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

Excuse the length. The basic idea is that for each letter, there are a number of criteria, kept in a separate table. The query created places each criteria in a field of the query, and each letter a row, hence the denormalized presentation.

Perhaps a crosstab query might present a more concise solution??

 

[SBM]

Used DOCMan's suggestion and it works a treat.

Thanks