To MVP John Spencer - Ranking query
in [Reports]
|
Prev: To MVP John Spencer - Report - ranking query
Next: Min Max options in Access 2007 adp reports not showing
From: Per Erik on 11 May 2010 07:38 Hey! Sorry my bad English! My Name Is Per Erik Løkken, and comes from Norway. I have been in contact with MVP Duane Hookom regarding an issue with a report in Access 2007. She tells me that you've had a post regarding the ranking query: “I believe you can do this without the subquery but using a totals query and a self join. MVP John Spencer has posted this type of ranking query in the past. You will get a more efficient and functional result as needed in the report record source.” My report is made with a subquery: SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B WHERE B.VEKT>A.VEKT)+1 AS Rank FROM HERRER AS A; I'm using SQL RANK because I want a proper ranking. Like this: Nr. Name Weight 1. Person1 - 350 gr 2 Person2 - 340 gr 2 Person3 - 340 gr 4. Person4 - 320 gr Those who have the same weight, should get the same “space-digits”. Like my example above. As you can see the No. 2 and 3 of the same weight (340 gr), and they will then be ranked No. 2 together. The next will then rank No. 4. And so I want to make a “mark” for the first third of the report, such as described below. There is marked with ****. This is because it is 1 / 3 to be rewarded. I've tried to do this with conditional formatting, but it did not work. 1 Jim Bekken Raufjøringen 9510 gr. 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr. 2 Kenneth Ottosen Gjøvik og Toten SFK 8105 gr. 4 Jørgen Langerud Raufjøringen 5245 gr. 5 Ove Lauten Gjerdrum 4745 gr. **** 6 Per E. Hellerud Eidsvoll JFF 4625 gr. 7 Frank Hønsen Gjerdrum SFK 4525 gr. 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr. 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr. 9 Anders Wold Oslo Sportsfiske 4255 gr. 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr. 12 Eric Olstad Toten JFF Lodd 2 3040 gr. 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr. 14 Remi A. Olsen SFK Pimpel Sør 2540 gr. 15 Paal Runden Gjøvik og Toten SFK 2495 gr. Can you help me with this? Regards, Per Erik Løkken - http://www.sports-reference.com/olympics/athletes/lo/per-erik-lokken-1.html ***
From: Duane Hookom on 11 May 2010 17:54
Here is an example from the Orders table in the Northwind sample database. It is sorted and ranked by Freight: SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.Freight, Count(Orders.OrderID) AS CountOfOrderID FROM Orders, Orders AS Orders_1 WHERE (((Orders_1.Freight)>[Orders].[Freight])) GROUP BY Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.Freight ORDER BY Orders.Freight DESC; -- Duane Hookom Microsoft Access MVP "Per Erik" wrote: > Hey! > Sorry my bad English! > > My Name Is Per Erik Løkken, and comes from Norway. I have been in contact > with MVP Duane Hookom regarding an issue with a report in Access 2007. > > She tells me that you've had a post regarding the ranking query: > > “I believe you can do this without the subquery but using a totals query and > a self join. MVP John Spencer has posted this type of ranking query in the > past. You will get a more efficient and functional result as needed in the > report record source.” > > My report is made with a subquery: > SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B > WHERE B.VEKT>A.VEKT)+1 AS Rank FROM HERRER AS A; > > I'm using SQL RANK because I want a proper ranking. > Like this: > Nr. Name Weight > 1. Person1 - 350 gr > 2 Person2 - 340 gr > 2 Person3 - 340 gr > 4. Person4 - 320 gr > Those who have the same weight, should get the same “space-digits”. Like my > example above. > As you can see the No. 2 and 3 of the same weight (340 gr), and they will > then be ranked No. 2 together. > > The next will then rank No. 4. > > And so I want to make a “mark” for the first third of the report, such as > described below. There is marked with ****. > > This is because it is 1 / 3 to be rewarded. > I've tried to do this with conditional formatting, but it did not work. > > > 1 Jim Bekken Raufjøringen 9510 gr. > 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr. > 2 Kenneth Ottosen Gjøvik og Toten SFK 8105 gr. > 4 Jørgen Langerud Raufjøringen 5245 gr. > 5 Ove Lauten Gjerdrum 4745 gr. > **** > 6 Per E. Hellerud Eidsvoll JFF 4625 gr. > 7 Frank Hønsen Gjerdrum SFK 4525 gr. > 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr. > 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr. > 9 Anders Wold Oslo Sportsfiske 4255 gr. > 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr. > 12 Eric Olstad Toten JFF Lodd 2 3040 gr. > 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr. > 14 Remi A. Olsen SFK Pimpel Sør 2540 gr. > 15 Paal Runden Gjøvik og Toten SFK 2495 gr. > > > Can you help me with this? > > > Regards, > > Per Erik Løkken - > http://www.sports-reference.com/olympics/athletes/lo/per-erik-lokken-1.html > *** |