Rank in a report

[peik]

Hi
Sorry my bad English! I hope however that someone understands my question and can give me an answer!
I am trying to create a program in Microsoft Office Access 2007.
I have 7 columns in my report: Name, No Mirrors, Judge 1, Judge 2, Judge 3, Klipp1 and POINTS.

I want to create a new column that is ranked by column POINTS.
And it should work like this:
1
2
3
3
5
So is the score on the third place in the POINTS equal, then the next will be 5

Can anyone help me with this?

 

[vbaInet]

You've told us what you would like it to look like but you've not shown us some raw data.

By the way, fields like Judge1, Judg2, Judge3 indicate that your table is not normalized.

 

[peik]

Sorry, there was a little off because I have translated from Norwegian into English.
I have six columns, and it looks like this:
Nr. Navn Dommer1 Dommer2 Dommer3 Poeng
1 Sigrun 10 12 12 34
2 Kari 12 10 9 31
3 Britt 9 13 9 31

As you can see is NOT number 2 and 3 the same place, even if they have the same score (Poeng) (31). What I want is to rank, so they both get second place. And then will the next on the list be fourth.
(Poeng are points) - (Norwegian).

Points (Poeng) are the sum of Dommer1 + Dommer2 + Dommer3

Hope this made it a little clearer what I want.

 

[vbaInet]

Right, so now what would be useful is if you show us what you would like this:

I have six columns, and it looks like this:
Nr. Navn Dommer1 Dommer2 Dommer3 Poeng
1 Sigrun 10 12 12 34
2 Kari 12 10 9 31
3 Britt 9 13 9 31

... to look like.

 

[peik]

Nr. Navn Dommer 1 Dommer 2 Dommer 3 Poeng
1 Sigrun 10 12 12 34
2 Kari 12 10 9 31
2 Britt 9 13 9 31
4 Jorunn 8 9 12 29
5 Geir 7 8 7 22

 

[peik]

Lokks like the attachment file is missing! But i should look like this:
Nr. Navn Dommer 1 Dommer 2 Dommer 3 Poeng
1 Sigrun 10 12 12 34
2 Kari 12 10 9 31
2 Britt 9 13 9 31
4 Jorunn 8 9 12 29
5 Geir 7 8 7 22

 

[peik]

Here is the Excel file!!

 

[vbaInet]

You have too few posts to be able to upload anything. 10 posts is the minimum, but here's how you can overcome that:

http://www.access-programmers.co.uk/forums/showthread.php?t=212122

That was just for your information, I understand what you're trying to do now.

How many Dommers do you have?
Do you know how to add them up in a query to get Poeng?

 

[peik]

Did you see the attached file now? (Klipp1Lærling.xls)

I have 3 Dommere.
Sum: [Tabell1]![Dommer1]+[Tabell1]![Dommer2]+[Tabell1]![Dommer3]

This works!

But I don`t know how to get the right Rank.

 

[vbaInet]

I see it Ok:

1. save the query and create a report based on the query.
2. Open the report in Design View and create a GROUPing for Poeng
3. Create a textbox in the Header section of the Poeng Group and call it txtGrpCount
4. Enter =1 in the Control Source of the textbox and set the Running Sum property to Over All
5. Create a textbox in the Detail section of the report and call it txtRank
6. Enter =[txtGrpCount] in the Control Source of txtRank. Leave the Running Sum as No.
7. Set the Visible property of the Poeng Group Header section to False.

Done.

 

[peik]

Thank you, but this did not work...
Now, everyone is number 1 in the report.(See the attached PNG file).
But if I Export the report to Excel, it is correct!(See the attached Excel file)
Have I done something wrong?

 

[vbaInet]

If there's any step you don't understand please ask. You didn't add a group for Poeng.

In a report there's a feature called Grouping & Sorting. Or you didn't put txtGrpCount textbox in the group's header section or you didn't refer to the textbox's value.

 

[peik]

You was right!! I didn't put txtGrpCount textbox in the group's header....
Now it is PERFECT!
Thank You very much!!

 

[vbaInet]

Happy to help peik!

 

[peik]

Hello again!
Now I saw something that is not correct. As you can see from the attachment PNG, the number one and two has the same points, and will then get place 1, and that is correct. But the next person should have place 3 - NOT place 2! Is it possible to fix it?

 

[vbaInet]

I'm afraid that doesn't make sense. You don't rank that way.

 

[peik]

And I`m afraid that is the correct way to do it!
Here is an example from the PGA tour in golf:
RANK PLAYER ROUND THRU TOTAL
T1 Ben Crane -9 F -16
T1 David Toms -7 F -16
T1 Mark Wilson -10 F -16
T4 Camilo Villegas -4 F -13
T4 Harris English -10 F -13

As you see the three first has the same points (16), and i s ranked no. 1. And then you see that the next on the list is ranked no. 4!! (Not no. 2)....
And the next one the list should be no. 6.
This is correct ranking.

 

[vbaInet]

They are just being weird!

Right, what I'm saying is that was not what you asked in your Original Post. Please try to be consistent for next time so we don't waste too much time giving a solution that isn't relevant. What do you want now?

 

[peik]

This is what I wrote in my Original Post:
I want to create a new column that is ranked by column POINTS.
And it should work like this:
1
2
3
3
5
So is the score on the third place in the POINTS equal, then the next will be 5.

I`m sorry if you feel that I waste your time!!!

 

[peik]

I try again. I want it to be ranked as Excel do it.