please need help RANK

[Andyjones]

Hi I have a report based on a query of calculated fields the report layout is this

Player Name Played Won Lost For Against +_ Points

Within this query I want to add a rank field to rank them in the order of highest points ie highest points =1, 2,3 etc with same points ranking joint 3rd.

I would like a function or expression that will do this for me I have attached my database to see if anyone can do this or if the layout needs to be changed please help.



My first table is player name that contains the list of players
The second table contains match data

Team Name
Played home Won Home Score 1 Score 2 Points Played Away Won Away Score 3 score 4 for example

in the points coloum 2 points for home win 3 points for away win the query then adds up all the data to show as mentione above but i would like a to rank them like you can do in excell.

If you wish me to send you copy of the database then please send me private message as i can not shrink it anymore to upload on to here

I want it a expression so that i can also creat a form called stats which will show played won lost points and rank in the league.

Need step by step instructions if possible

if you need to see database send email address and I WILL THEN EMAIL IT TO YOU.

[June7]

Zip the project, is it less than 2mb? Can attach zip folder up to 2mb.

See if this helps.
http://support.microsoft.com/default...b;en-us;208946
http://allenbrowne.com/ranking.html

[Andyjones]

hi June 7 I did try to zip it but its 2.28mb so if you could help with this issue please pm me with your email and i should then be able to send you the database so a rank field can be added.

[June7]

Did you explore the links I posted? I prefer you provide file through the forum. You could extract the relevent tables (minimal data) to another file and attach that. I have never needed to do ranking of my data, have only dealt with this technique for posted questions. I always have to go back to the sources I posted and refresh myself on it.

[Andyjones]

Hi June 7 I have had a look but its not very self explanatory and i do not understand sql that well so i have uploaded my database here for you.

If design needs changing to allow a rank expression in a query then please do so of if you could be so kind in adding the code yourself.

It would be much appreciated.

jonesstanwellatskydotcom

[Andyjones]

June 7

If you give me a list of instructions to follow/ step by step to add the rank to a query i would be able to do it myself.

[June7]

I don't have it memorized. Do what I would have to do, review the links and figure it out. Here is the last time I dealt with similar question: http://forums.aspfree.com/microsoft-...ge-440477.html

If I have time later this week will look at your data.

[Andyjones]

Ji June 7 Thanks for the info I have had a look at the links you have given me but unfortunately I do not understand sql and it is not very self explanatory so if you do get time to have a look at what i have uploaded I would be very grateful.

[June7]

Try (copy/paste this into the SQL View window of query designer):
SELECT x.[Player Name], x.Points, (SELECT Count(*)+1 FROM [Sunday Averages] WHERE Points < x.Points) AS Rank
FROM [Sunday Averages] AS x;