Rank (Simple Order)

  • Thread starter Thread starter John R. Baker
  • Start date Start date
J

John R. Baker

I have a complex query that computes a priority score based on importance.
The name of the query is "qryProjectRank". The idea is that projects with
the highest "ProjectScore" should be tackled first. The value of the
"ProjectScore" has already been pre-computed by an earlier linked query, and
is sorted in descending order.

My output looks something like this:

[ProjectName] [ProjectScore]
Project_E 57
Project_H 52
Project_B 51
Project_G 46
Project_C 46
Project_A 32
Project_D 23
Project_F 18

I would like to add another column called "Rank", as shown below. (In this
example, "Project_B" is third on the list, behind "Project_E" and
"Project_H".

[ProjectName] [ProjectScore] [Rank]
Project_E 57 1
Project_H 52 2
Project_B 51 3
Project_G 46 4
Project_C 46 4
Project_A 32 6
Project_D 23 7
Project_F 18 8

Alternately, I would like to add a SECOND ranking, called "Rank2". It is
the same as the first "Rank" except that duplicate "ProjectScore" values do
not get the same "Rank" value. See the following example:

[ProjectName] [ProjectScore] [Rank] [Rank2]
Project_E 57 1 1
Project_H 52 2 2
Project_B 51 3 3
Project_G 46 4 4
Project_C 46 4 5
Project_A 32 6 6
Project_D 23 7 7
Project_F 18 8 8

Can someone help me create a query that will give me the "Rank" and "Rank2"
output I want?

I have studied the example in "Microsoft Knowledge Base Article - 120608" at
the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;120608&Product=acc
I am unable to get it to work with this technique.

Thanks!

- John R. Baker
 
John,

Are you in for some code?
Make a new general module (or use an existing one if you like, just make
sure you put the public variable declarations at the toip in this case) and
paste in the following code:

Public Rank As Long
Public Rank2 As Long
Public RCount As Long
Public PScore As Double

Function Get_Rank(PS as Double) As Long
RCount = RCount + 1
If PS > PScore Then Rank2 = RCount
PScore = PS
Get_Rank = Rank2
End Function

Function Get_Rank2(PS as Double) As Long
Rank2 = Rank2 + 1
Get_Rank2 = Rank2
End Function

Function Run_Query()
Rank = 0
Rank2 = 0
RCount = 0
PScore = 0
DoCmd.OpenQuery "QueryName" '(change QueryName to the actual query name).
End Function

Now return to your query design and add the following two calculated fields:

Rank: Get_Rank([ProjectScore])
Rank2: Get_Rank2([ProjectScore])

The first two functions calculate the ranks in each query record. The third
is utilised to reset the public variables before the query is run each time,
so instead of opening the query directly from the database window, you need
to do it by executing this code instead.
If you plan to run the query by means of a command button on a form, then
just use a:
Call Run_Query
statement in your command button code. If, on the other hand, you were only
going to run the query directly from the database window, then having to
open the VBA editor and run the code manually instead is not a very elegant
approach; instead, you can make a macro with a single action RunCode,
argument Run_Query(), and run the macro from the database window instead of
the query itself.

HTH,
Nikos

John R. Baker said:
I have a complex query that computes a priority score based on importance.
The name of the query is "qryProjectRank". The idea is that projects with
the highest "ProjectScore" should be tackled first. The value of the
"ProjectScore" has already been pre-computed by an earlier linked query, and
is sorted in descending order.

My output looks something like this:

[ProjectName] [ProjectScore]
Project_E 57
Project_H 52
Project_B 51
Project_G 46
Project_C 46
Project_A 32
Project_D 23
Project_F 18

I would like to add another column called "Rank", as shown below. (In this
example, "Project_B" is third on the list, behind "Project_E" and
"Project_H".

[ProjectName] [ProjectScore] [Rank]
Project_E 57 1
Project_H 52 2
Project_B 51 3
Project_G 46 4
Project_C 46 4
Project_A 32 6
Project_D 23 7
Project_F 18 8

Alternately, I would like to add a SECOND ranking, called "Rank2". It is
the same as the first "Rank" except that duplicate "ProjectScore" values do
not get the same "Rank" value. See the following example:

[ProjectName] [ProjectScore] [Rank] [Rank2]
Project_E 57 1 1
Project_H 52 2 2
Project_B 51 3 3
Project_G 46 4 4
Project_C 46 4 5
Project_A 32 6 6
Project_D 23 7 7
Project_F 18 8 8

Can someone help me create a query that will give me the "Rank" and "Rank2"
output I want?

I have studied the example in "Microsoft Knowledge Base Article - 120608" at
the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;120608&Product=acc
I am unable to get it to work with this technique.

Thanks!

- John R. Baker
Click to expand...
 
Nikos,

Thanks! I will try that.

- John R. Baker



Nikos Yannacopoulos said:
John,

Are you in for some code?
Make a new general module (or use an existing one if you like, just make
sure you put the public variable declarations at the toip in this case) and
paste in the following code:

Public Rank As Long
Public Rank2 As Long
Public RCount As Long
Public PScore As Double

Function Get_Rank(PS as Double) As Long
RCount = RCount + 1
If PS > PScore Then Rank2 = RCount
PScore = PS
Get_Rank = Rank2
End Function

Function Get_Rank2(PS as Double) As Long
Rank2 = Rank2 + 1
Get_Rank2 = Rank2
End Function

Function Run_Query()
Rank = 0
Rank2 = 0
RCount = 0
PScore = 0
DoCmd.OpenQuery "QueryName" '(change QueryName to the actual query name).
End Function

Now return to your query design and add the following two calculated fields:

Rank: Get_Rank([ProjectScore])
Rank2: Get_Rank2([ProjectScore])

The first two functions calculate the ranks in each query record. The third
is utilised to reset the public variables before the query is run each time,
so instead of opening the query directly from the database window, you need
to do it by executing this code instead.
If you plan to run the query by means of a command button on a form, then
just use a:
Call Run_Query
statement in your command button code. If, on the other hand, you were only
going to run the query directly from the database window, then having to
open the VBA editor and run the code manually instead is not a very elegant
approach; instead, you can make a macro with a single action RunCode,
argument Run_Query(), and run the macro from the database window instead of
the query itself.

HTH,
Nikos

John R. Baker said:
I have a complex query that computes a priority score based on importance.
The name of the query is "qryProjectRank". The idea is that projects with
the highest "ProjectScore" should be tackled first. The value of the
"ProjectScore" has already been pre-computed by an earlier linked query, and
is sorted in descending order.

My output looks something like this:

[ProjectName] [ProjectScore]
Project_E 57
Project_H 52
Project_B 51
Project_G 46
Project_C 46
Project_A 32
Project_D 23
Project_F 18

I would like to add another column called "Rank", as shown below. (In this
example, "Project_B" is third on the list, behind "Project_E" and
"Project_H".

[ProjectName] [ProjectScore] [Rank]
Project_E 57 1
Project_H 52 2
Project_B 51 3
Project_G 46 4
Project_C 46 4
Project_A 32 6
Project_D 23 7
Project_F 18 8

Alternately, I would like to add a SECOND ranking, called "Rank2". It is
the same as the first "Rank" except that duplicate "ProjectScore" values do
not get the same "Rank" value. See the following example:

[ProjectName] [ProjectScore] [Rank] [Rank2]
Project_E 57 1 1
Project_H 52 2 2
Project_B 51 3 3
Project_G 46 4 4
Project_C 46 4 5
Project_A 32 6 6
Project_D 23 7 7
Project_F 18 8 8

Can someone help me create a query that will give me the "Rank" and "Rank2"
output I want?

I have studied the example in "Microsoft Knowledge Base Article -
Click to expand...
120608"
at
the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;120608&Product=acc
I am unable to get it to work with this technique.

Thanks!

- John R. Baker
Click to expand...
Click to expand...
 
Back
Top