Modify Ranking Code

[mccallen60]

I currently have a table with three fields: ChgMS, Position and Rank (see
example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my ChgMS
to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.

 

[Ken Snell]

Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position]) +
(SELECT Count(*) from [TableName] Where [Position] =
[aliasTableName].[Position] AND [ChgMS] >=
[aliasTableName].[ChgMS])

Try the above. You might need to reverse the >= logic to <= logic after you
view your results.

 

[John Spencer]

Does aliasTableName have ChgMS in it or a way to derive it? IF so, then you
might try the following.

Rank: (Select Count (*) from [TableName] Where [Position] + ChgMs <
[aliasTableName].[Position] + [aliasTableName].[ChgMs])+1

If not, then there is really no good way to do this that I can see.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mccallen60]

John,
Thank you for your prompt reply and suggestion. Your solution almost works.
The ChgMS is in the alias table. However, when I apply your suggestion, the
data returns like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 5
0.0254 11 6

The data needs to be returned like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

The 0.0296 ChgMS is higher than the 0.0251. So 0.0296 should be ranked 4 and
0.0251 should be ranked 5. Your solution has it opposite. If you have a way
this can be corrected, please let me know.

Thanks for your help.

Does aliasTableName have ChgMS in it or a way to derive it? IF so, then you
might try the following.

Rank: (Select Count (*) from [TableName] Where [Position] + ChgMs <
[aliasTableName].[Position] + [aliasTableName].[ChgMs])+1

If not, then there is really no good way to do this that I can see.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I currently have a table with three fields: ChgMS, Position and Rank (see
example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my ChgMS
to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.

.

 

[mccallen60]

Ken,
Thank you very much for your suggestion. However, it's not quite what I'm
looking for. Your suggestion gives a rank of "1" to every record where the
position is not tied. Where the position is tied, it ranks it accordingly
with a "2", "3", etc using the ChgMS as a tie breaker. I'm looking for a
straight ranking of "1", "2", "3", "4", ect for each of my records. ie:
finding a better way of ranking.
Please let me know if you have any other suggestions.
Thank you very much for all your help.

Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position]) +
(SELECT Count(*) from [TableName] Where [Position] =
[aliasTableName].[Position] AND [ChgMS] >=
[aliasTableName].[ChgMS])

Try the above. You might need to reverse the >= logic to <= logic after you
view your results.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I currently have a table with three fields: ChgMS, Position and Rank (see
example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my
ChgMS
to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.

.

 

[Ken Snell]

Post the entire SQL statement of your query that you say is giving a rank of
1 for each position that is there just once. All I did was add an "adding
number" to what you were using, which you said was working almost right. So
I'm puzzled that it has stopped working from what you were getting.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,
Thank you very much for your suggestion. However, it's not quite what I'm
looking for. Your suggestion gives a rank of "1" to every record where the
position is not tied. Where the position is tied, it ranks it accordingly
with a "2", "3", etc using the ChgMS as a tie breaker. I'm looking for a
straight ranking of "1", "2", "3", "4", ect for each of my records. ie:
finding a better way of ranking.
Please let me know if you have any other suggestions.
Thank you very much for all your help.

Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position]) +
(SELECT Count(*) from [TableName] Where [Position] =
[aliasTableName].[Position] AND [ChgMS] >=
[aliasTableName].[ChgMS])

Try the above. You might need to reverse the >= logic to <= logic after
you
view your results.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I currently have a table with three fields: ChgMS, Position and Rank
(see
example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my
ChgMS
to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.

.

 

[Ken Snell]

I just tested the expression that I gave you, and it returns the correct
results. Must be a typo in your query.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Post the entire SQL statement of your query that you say is giving a rank
of 1 for each position that is there just once. All I did was add an
"adding number" to what you were using, which you said was working almost
right. So I'm puzzled that it has stopped working from what you were
getting.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,
Thank you very much for your suggestion. However, it's not quite what I'm
looking for. Your suggestion gives a rank of "1" to every record where
the
position is not tied. Where the position is tied, it ranks it accordingly
with a "2", "3", etc using the ChgMS as a tie breaker. I'm looking for a
straight ranking of "1", "2", "3", "4", ect for each of my records. ie:
finding a better way of ranking.
Please let me know if you have any other suggestions.
Thank you very much for all your help.

Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position]) +
(SELECT Count(*) from [TableName] Where [Position] =
[aliasTableName].[Position] AND [ChgMS] >=
[aliasTableName].[ChgMS])

Try the above. You might need to reverse the >= logic to <= logic after
you
view your results.
--

Ken Snell
http://www.accessmvp.com/KDSnell/





I currently have a table with three fields: ChgMS, Position and Rank
(see
example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my
ChgMS
to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.


.

 

[mccallen60]

Ken, I found my typo and your suggestion works like a charm! Thank you for
all your help.

I just tested the expression that I gave you, and it returns the correct
results. Must be a typo in your query.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Post the entire SQL statement of your query that you say is giving a rank
of 1 for each position that is there just once. All I did was add an
"adding number" to what you were using, which you said was working almost
right. So I'm puzzled that it has stopped working from what you were
getting.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,
Thank you very much for your suggestion. However, it's not quite what I'm
looking for. Your suggestion gives a rank of "1" to every record where
the
position is not tied. Where the position is tied, it ranks it accordingly
with a "2", "3", etc using the ChgMS as a tie breaker. I'm looking for a
straight ranking of "1", "2", "3", "4", ect for each of my records. ie:
finding a better way of ranking.
Please let me know if you have any other suggestions.
Thank you very much for all your help.

:

Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position]) +
(SELECT Count(*) from [TableName] Where [Position] =
[aliasTableName].[Position] AND [ChgMS] >=
[aliasTableName].[ChgMS])

Try the above. You might need to reverse the >= logic to <= logic after
you
view your results.
--

Ken Snell
http://www.accessmvp.com/KDSnell/





I currently have a table with three fields: ChgMS, Position and Rank
(see
example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my
ChgMS
to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.


.

.

 

[John Spencer]

You might try the following:

Rank: (Select Count (*) from [TableName] Where [Position] - ChgMs <
[aliasTableName].[Position] - [aliasTableName].[ChgMs])+1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Does aliasTableName have ChgMS in it or a way to derive it? IF so, then
you might try the following.

Rank: (Select Count (*) from [TableName] Where [Position] + ChgMs <
[aliasTableName].[Position] + [aliasTableName].[ChgMs])+1

If not, then there is really no good way to do this that I can see.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I currently have a table with three fields: ChgMS, Position and Rank
(see example with test data below)

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0251 9 4
0.0296 9 4
0.0254 11 6

I currently use this logic in my query to create the Rank field:
Rank: (Select Count (*) from [TableName] Where [Position] <
[aliasTableName].[Position])+1

As you can see, where the Position is tied, so is the Rank. I want my
ChgMS to be the tie breaker. So the end result should look like this:

ChgMS Position Rank
0.0576 2 1
0.0384 5 2
0.0452 6 3
0.0296 9 4
0.0251 9 5
0.0254 11 6

How can I modify my logic to accomplish this?
Thank you.