Rank function -rank by who has most points/quickest time?!

[Sharky II]

Hi guys

I need to rank records by total number of points that a team has acquired (i think i've already got the 'total' summed part sorted, and they will be sorted in ascending/decending order - this already works and so it just needs to number them). I have tried the sample query exe from microsoft, and checked the knowledge base page, but am still finding it a bit difficult.

For some 'events' that will be entered into the database, I will also need to somehow rank teams on who ever completed the event in the quickest time

I understand that i have to make a query of a query of a table, but i'm getting lost in the process. I need it not to tell me how many events the team has done, but instead, it should just look at the numbers and then rank the teams accordingly, and dynamically (so that it changes etc).

Tried to follow the instructions in teh knowledge base but got lost as it was for a different DB. Can anyone help simplify/explain the process to me better please?

Many thanks

Edd

 

[ReAn]

You need a query of the events table, put a criteria on the teamid to filter for whichever team you need, then make a field called tDiff and make it's definition:

DateDiff("s",Date() + [TimeStart],Date() + [TimeEnd])

Sort by decending order.

 

[Sharky II]

hi

sorry but i don't understand this at all

I need to automatically allocate cumulative scores in accordance with rankings. For example, if Team A achieves the fastest time in a run on Saturday, they are given a number 1 ranking and would normally be awarded 100 points. But if Team B then completes the run in a faster time on the Sunday, Team A loses its number 1 ranking and the 100 points and drops to second place (with 95 points).

Basically am unsure as to how to rank teams via comparing values of time (or 'points' for other events, like the IQ test event), and then somehow allocate a certain number of points depending on this ranking... and the ranking can change at any time!!!

Would really appreciate any more info on how this could be achieved

Cheers!

Eddie

 

[Sharky II]

of course the ranking and allocation of points can be done at the end, when all teams have done the activities, if it is easier that way?

i guess you can just do sort by descending for the sorting, but to allocate rank numbers and the points depending on their rank...

edit - i found out how to rank stuff here: http://support.microsoft.com/default.aspx?scid=kb;en-us;208946&Product=acc2000

however i am still stuck on how to allocate 100 points to first place, 95 to second place, 90 to third, etc - dynamically.

anyone?

 

[ssmithri]

points:IIf([rank]=1,100,IIf([rank]=2,75,IIf([rank]=3,50,IIf([rank]=4,25,IIf([rank]=5,10,0)))))