ACCESS FEED: Re: [MS_AccessPros] RANKING

Thanks Duanne. I took your advice to use Union Query and thereafter used the code below. I was able to achieve a result very close to my goal . Ade

UNION

SELECT ID, 1 as Factory, [Factory1] as TheValue

FROM Ranktest

UNION ALL

SELECT ID, 2, [Factory2]

FROM Ranktest

UNION ALL

SELECT ID, 3, [Factory3]

FROM Ranktest

UNION ALL

SELECT ID, 4, [Factory4]

FROM Ranktest

UNION ALL SELECT ID, 5, [Factory5]

FROM Ranktest

UNION ALL SELECT ID,6,[Factory6]

FROM Ranktest;

UNION ALL SELECT ID,7,[Factory7]

FROM Ranktest;

SELECT

SELECT t1.ID, t1.Factory, t1.TheValue, COUNT(*) AS Rank

FROM Ranking AS t1 LEFT JOIN Ranking AS t2 ON (t1.TheValue >= t2.TheValue) AND (t1.ID = t2.ID)

GROUP BY t1.ID, t1.Factory, t1.TheValue;

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: ms_access_professionals@yahoogroups.com
Sent: Friday, 15 April 2016, 14:17
Subject: Re: [MS_AccessPros] RANKING

IMO that is a spreadsheet not a relational database. Use a union query to normalize if you can't change the structure.

I'm about to board a plane so if someone else doesn't answer, I'll look at this later.

Sent from Outlook Mobile

On Fri, Apr 15, 2016 at 5:57 AM -0700, "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

No Mistake. Will this be applicable if I am evaluating three products at once as per below.?

	Factory1	Factory2	Factory3	Factory4	Factory5	Factory6	Factory7	Rank1	Rank2	Rank3	Rank4	Rank5	Rank6	Rank7
EngineOil	40	50	35	42	40	41	43	2.5	7	1	5	2.5	4	6
SeatBelt	5	7	8	6	6	5	4	2.5	6	7	4.5	4.5	2.5	1
GearBox	250	253	251	260	253	253	255	1	4	2	7	4	4	6

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Friday, 15 April 2016, 13:38
Subject: RE: [MS_AccessPros] RANKING

Do you actually have fields with factory names or is that a mistake? If they are across then you use a normalizing union query to get a properly structured resultset and then using the subquery to get the ranking. If you need the results back into a spreadsheet format then use a crosstab query.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 15 Apr 2016 10:16:55 +0000
Subject: Re: [MS_AccessPros] RANKING

Hi Duanne. Can we up the ante a bit. Consider the hypothetical scenario below with the rank. It shows the cost of producing engine oil in 7 different factories of a company.

The ranks to the right in ascending order which is the desired result were produced by using the Excel function RANK.AVG for which I could not see the equivalent in Access.

Could you please tweak the solution to replicate the result ? Thanks. Ade.

	Factory1	Factory2	Factory3	Factory4	Factory5	Factory6	Factory7	Rank1	Rank2	Rank3	Rank4	Rank5	Rank6	Rank7
Engine Oil	40	50	35	42	40	41	43	2.5	7	1	5	2.5	4	6

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, 13 April 2016, 18:45
Subject: RE: [MS_AccessPros] RANKING

Ade,

The typical method using SQL like:

SELECT tblAde.StudentName, tblAde.Score, (SELECT COUNT(*) FROM tblAde A WHERE A.Score <=tblAde.Score ) AS Rank

FROM tblAde;

This doesn't account for your using ".5" for ties. Before working on any type of solution for this, we would need to know what this would like like if there is a 3-way tie or even more.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 13 Apr 2016 13:40:55 +0000
Subject: Re: [MS_AccessPros] RANKING

Thanks. It easy to see the desired result from this example which is highly simplified. I will be dealing large data where visual inspection is impractical. What I am getting at is the query expression in design or sql view of MSAccess that will return the desired answer given the example in this case. Ade .

From: "Barry White imtigerwords@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, 13 April 2016, 14:30
Subject: Re: [MS_AccessPros] RANKING

I did not mention sorting at all. I explained ranking. I didn't say you had to sort the scores from highest to lowest or lowest to highest. I merely mentioned that if it were the game of golf, lowest score would be best, if it were test scores, 0 to 100%, highest would be the best score.

Whichever way is the BEST score (high or low), then you create your ranking formula based upon that.
I also provided you with the two ways that I personally calculate ranking.

Eric Lutz
--------------------------------------------
On Wed, 4/13/16, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Subject: Re: [MS_AccessPros] RANKING
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, April 13, 2016, 5:22 AM

Thanks Eric. It's not SORTING I am talking
about but
RANKING. The ranking field is a calculated control I am
trying to achieve in
Access and for which I need help. I included it to show the
desired final
result.

Ade.

From: "Barry White
imtigerwords@yahoo.com [MS_Access_Professionals]"
<MS_Access_Professionals@yahoogroups.com>
To: yahoogroups
<ms_access_professionals@yahoogroups.com>;
MS_Access_Professionals@yahoogroups.com

Sent:
Wednesday, 13 April 2016, 1:36
Subject: Re:
[MS_AccessPros] RANKING

It
appears the lower the score the better, like in the sport
golf, which I cannot play very well.

If the SCORE column is how you are evaluating ranking, then
usually the higher the score the better (like on test scores
in school). But it looks like the lower the score the better
in this ranking example.

But personally, when I do a ranking, I would not give Excel
and Notepad both a 4.5, they would each get a ranking of 4
from me. I would leave ranks at whole numbers. But they
are right whoever did this, you would then jump from a
ranking of 4 to a ranking of 6 for Harry, since there were
two names tied with the same 4 score.

As an aside, the other way I do rankings is that I take the
scores, whatever they happen to be, and if the HIGHEST score
is considered better, whatever that highest score happens to
be, I set it artificially as my 100% or as the denominator.
So every subsequent score that is lower than the highest
score would have the following formula:

Tom 98

Dick 55

Harry 75

Outlook 58

Excel 70

Publisher 61

Notepad 70

Tom = 100% or 100

Harry's formula would be (75 / 98) * 100

Outlooks formula would be (58 / 98) * 100

etc,

This way you can see just how far behind the followers are
from the leader in terms of percentage differential.

Eric Lutz

--------------------------------------------

On Tue, 4/12/16, Adeboyejo Oyenuga aoye_99@yahoo.co.uk
[MS_Access_Professionals]
<MS_Access_Professionals@yahoogroups.com> wrote:

Subject: [MS_AccessPros] RANKING

To: "yahoogroups"
<ms_access_professionals@yahoogroups.com>

Date: Tuesday, April 12, 2016, 7:41 PM

Hi All,

would

you give a hint as to the achievement of RANK as in column
3

in the query grid if the information in columns 1 &
2

are available ?

Pls

see below.

Thanks.

Ade

StudentNameScoreRankTom987Dick551Harry756Outlook582Excel704.5Publisher613Notepad704.5

#yiv1361616416 #yiv1361616416 --

#yiv1361616416ygrp-mkp {

border:1px solid #d8d8d8;font-family:Arial;margin:10px

0;padding:0 10px;}

#yiv1361616416 #yiv1361616416ygrp-mkp hr {

border:1px solid #d8d8d8;}

#yiv1361616416 #yiv1361616416ygrp-mkp #yiv1361616416hd {

color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px

0;}

#yiv1361616416 #yiv1361616416ygrp-mkp #yiv1361616416ads
{

margin-bottom:10px;}

#yiv1361616416 #yiv1361616416ygrp-mkp .yiv1361616416ad {

padding:0 0;}

#yiv1361616416 #yiv1361616416ygrp-mkp .yiv1361616416ad p
{

margin:0;}

#yiv1361616416 #yiv1361616416ygrp-mkp .yiv1361616416ad a
{

color:#0000ff;text-decoration:none;}

#yiv1361616416 #yiv1361616416ygrp-sponsor

#yiv1361616416ygrp-lc {

font-family:Arial;}

#yiv1361616416 #yiv1361616416ygrp-sponsor

#yiv1361616416ygrp-lc #yiv1361616416hd {

margin:10px

0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv1361616416 #yiv1361616416ygrp-sponsor

#yiv1361616416ygrp-lc .yiv1361616416ad {

margin-bottom:10px;padding:0 0;}

#yiv1361616416 #yiv1361616416actions {

font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv1361616416 #yiv1361616416activity {

background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv1361616416 #yiv1361616416activity span {

font-weight:700;}

#yiv1361616416 #yiv1361616416activity span:first-child {

text-transform:uppercase;}

#yiv1361616416 #yiv1361616416activity span a {

color:#5085b6;text-decoration:none;}

#yiv1361616416 #yiv1361616416activity span span {

color:#ff7900;}

#yiv1361616416 #yiv1361616416activity span

.yiv1361616416underline {

text-decoration:underline;}

#yiv1361616416 .yiv1361616416attach {

clear:both;display:table;font-family:Arial;font-size:12px;padding:10px

0;width:400px;}

#yiv1361616416 .yiv1361616416attach div a {

text-decoration:none;}

#yiv1361616416 .yiv1361616416attach img {

border:none;padding-right:5px;}

#yiv1361616416 .yiv1361616416attach label {

display:block;margin-bottom:5px;}

#yiv1361616416 .yiv1361616416attach label a {

text-decoration:none;}

#yiv1361616416 blockquote {

margin:0 0 0 4px;}

#yiv1361616416 .yiv1361616416bold {

font-family:Arial;font-size:13px;font-weight:700;}

#yiv1361616416 .yiv1361616416bold a {

text-decoration:none;}

#yiv1361616416 dd.yiv1361616416last p a {

font-family:Verdana;font-weight:700;}

#yiv1361616416 dd.yiv1361616416last p span {

margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv1361616416 dd.yiv1361616416last p

span.yiv1361616416yshortcuts {

margin-right:0;}

#yiv1361616416 div.yiv1361616416attach-table div div a {

text-decoration:none;}

#yiv1361616416 div.yiv1361616416attach-table {

width:400px;}

#yiv1361616416 div.yiv1361616416file-title a,
#yiv1361616416

div.yiv1361616416file-title a:active, #yiv1361616416

div.yiv1361616416file-title a:hover, #yiv1361616416

div.yiv1361616416file-title a:visited {

text-decoration:none;}

#yiv1361616416 div.yiv1361616416photo-title a,

#yiv1361616416 div.yiv1361616416photo-title a:active,

#yiv1361616416 div.yiv1361616416photo-title a:hover,

#yiv1361616416 div.yiv1361616416photo-title a:visited {

text-decoration:none;}

#yiv1361616416 div#yiv1361616416ygrp-mlmsg

#yiv1361616416ygrp-msg p a span.yiv1361616416yshortcuts
{

font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv1361616416 .yiv1361616416green {

color:#628c2a;}

#yiv1361616416 .yiv1361616416MsoNormal {

margin:0 0 0 0;}

#yiv1361616416 o {

font-size:0;}

#yiv1361616416 #yiv1361616416photos div {

float:left;width:72px;}

#yiv1361616416 #yiv1361616416photos div div {

border:1px solid

#666666;height:62px;overflow:hidden;width:62px;}

#yiv1361616416 #yiv1361616416photos div label {

color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv1361616416 #yiv1361616416reco-category {

font-size:77%;}

#yiv1361616416 #yiv1361616416reco-desc {

font-size:77%;}

#yiv1361616416 .yiv1361616416replbq {

margin:4px;}

#yiv1361616416 #yiv1361616416ygrp-actbar div a:first-child
{

margin-right:2px;padding-right:5px;}

#yiv1361616416 #yiv1361616416ygrp-mlmsg {

font-size:13px;font-family:Arial, helvetica, clean,

sans-serif;}

#yiv1361616416 #yiv1361616416ygrp-mlmsg table {

font-size:inherit;font:100%;}

#yiv1361616416 #yiv1361616416ygrp-mlmsg select,

#yiv1361616416 input, #yiv1361616416 textarea {

font:99% Arial, Helvetica, clean, sans-serif;}

#yiv1361616416 #yiv1361616416ygrp-mlmsg pre,
#yiv1361616416

code {

font:115% monospace;}

#yiv1361616416 #yiv1361616416ygrp-mlmsg * {

line-height:1.22em;}

#yiv1361616416 #yiv1361616416ygrp-mlmsg #yiv1361616416logo
{

padding-bottom:10px;}

#yiv1361616416 #yiv1361616416ygrp-msg p a {

font-family:Verdana;}

#yiv1361616416 #yiv1361616416ygrp-msg

p#yiv1361616416attach-count span {

color:#1E66AE;font-weight:700;}

#yiv1361616416 #yiv1361616416ygrp-reco

#yiv1361616416reco-head {

color:#ff7900;font-weight:700;}

#yiv1361616416 #yiv1361616416ygrp-reco {

margin-bottom:20px;padding:0px;}

#yiv1361616416 #yiv1361616416ygrp-sponsor
#yiv1361616416ov

li a {

font-size:130%;text-decoration:none;}

#yiv1361616416 #yiv1361616416ygrp-sponsor
#yiv1361616416ov

li {

font-size:77%;list-style-type:square;padding:6px 0;}

#yiv1361616416 #yiv1361616416ygrp-sponsor
#yiv1361616416ov

ul {

margin:0;padding:0 0 0 8px;}

#yiv1361616416 #yiv1361616416ygrp-text {

font-family:Georgia;}

#yiv1361616416 #yiv1361616416ygrp-text p {

margin:0 0 1em 0;}

#yiv1361616416 #yiv1361616416ygrp-text tt {

font-size:120%;}

#yiv1361616416 #yiv1361616416ygrp-vital ul li:last-child
{

border-right:none !important;

}

#yiv1361616416

#yiv7451746540 #yiv7451746540 --
#yiv7451746540ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv7451746540 #yiv7451746540ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv7451746540 #yiv7451746540ygrp-mkp #yiv7451746540hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv7451746540 #yiv7451746540ygrp-mkp #yiv7451746540ads {
margin-bottom:10px;}

#yiv7451746540 #yiv7451746540ygrp-mkp .yiv7451746540ad {
padding:0 0;}

#yiv7451746540 #yiv7451746540ygrp-mkp .yiv7451746540ad p {
margin:0;}

#yiv7451746540 #yiv7451746540ygrp-mkp .yiv7451746540ad a {
color:#0000ff;text-decoration:none;}
#yiv7451746540 #yiv7451746540ygrp-sponsor
#yiv7451746540ygrp-lc {
font-family:Arial;}

#yiv7451746540 #yiv7451746540ygrp-sponsor
#yiv7451746540ygrp-lc #yiv7451746540hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv7451746540 #yiv7451746540ygrp-sponsor
#yiv7451746540ygrp-lc .yiv7451746540ad {
margin-bottom:10px;padding:0 0;}

#yiv7451746540 #yiv7451746540actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv7451746540 #yiv7451746540activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv7451746540 #yiv7451746540activity span {
font-weight:700;}

#yiv7451746540 #yiv7451746540activity span:first-child {
text-transform:uppercase;}

#yiv7451746540 #yiv7451746540activity span a {
color:#5085b6;text-decoration:none;}

#yiv7451746540 #yiv7451746540activity span span {
color:#ff7900;}

#yiv7451746540 #yiv7451746540activity span
.yiv7451746540underline {
text-decoration:underline;}

#yiv7451746540 .yiv7451746540attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv7451746540 .yiv7451746540attach div a {
text-decoration:none;}

#yiv7451746540 .yiv7451746540attach img {
border:none;padding-right:5px;}

#yiv7451746540 .yiv7451746540attach label {
display:block;margin-bottom:5px;}

#yiv7451746540 .yiv7451746540attach label a {
text-decoration:none;}

#yiv7451746540 blockquote {
margin:0 0 0 4px;}

#yiv7451746540 .yiv7451746540bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv7451746540 .yiv7451746540bold a {
text-decoration:none;}

#yiv7451746540 dd.yiv7451746540last p a {
font-family:Verdana;font-weight:700;}

#yiv7451746540 dd.yiv7451746540last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv7451746540 dd.yiv7451746540last p
span.yiv7451746540yshortcuts {
margin-right:0;}

#yiv7451746540 div.yiv7451746540attach-table div div a {
text-decoration:none;}

#yiv7451746540 div.yiv7451746540attach-table {
width:400px;}

#yiv7451746540 div.yiv7451746540file-title a, #yiv7451746540
div.yiv7451746540file-title a:active, #yiv7451746540
div.yiv7451746540file-title a:hover, #yiv7451746540
div.yiv7451746540file-title a:visited {
text-decoration:none;}

#yiv7451746540 div.yiv7451746540photo-title a,
#yiv7451746540 div.yiv7451746540photo-title a:active,
#yiv7451746540 div.yiv7451746540photo-title a:hover,
#yiv7451746540 div.yiv7451746540photo-title a:visited {
text-decoration:none;}

#yiv7451746540 div#yiv7451746540ygrp-mlmsg
#yiv7451746540ygrp-msg p a span.yiv7451746540yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv7451746540 .yiv7451746540green {
color:#628c2a;}

#yiv7451746540 .yiv7451746540MsoNormal {
margin:0 0 0 0;}

#yiv7451746540 o {
font-size:0;}

#yiv7451746540 #yiv7451746540photos div {
float:left;width:72px;}

#yiv7451746540 #yiv7451746540photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv7451746540 #yiv7451746540photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv7451746540 #yiv7451746540reco-category {
font-size:77%;}

#yiv7451746540 #yiv7451746540reco-desc {
font-size:77%;}

#yiv7451746540 .yiv7451746540replbq {
margin:4px;}

#yiv7451746540 #yiv7451746540ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv7451746540 #yiv7451746540ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv7451746540 #yiv7451746540ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv7451746540 #yiv7451746540ygrp-mlmsg select,
#yiv7451746540 input, #yiv7451746540 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv7451746540 #yiv7451746540ygrp-mlmsg pre, #yiv7451746540
code {
font:115% monospace;}

#yiv7451746540 #yiv7451746540ygrp-mlmsg * {
line-height:1.22em;}

#yiv7451746540 #yiv7451746540ygrp-mlmsg #yiv7451746540logo {
padding-bottom:10px;}

#yiv7451746540 #yiv7451746540ygrp-msg p a {
font-family:Verdana;}

#yiv7451746540 #yiv7451746540ygrp-msg
p#yiv7451746540attach-count span {
color:#1E66AE;font-weight:700;}

#yiv7451746540 #yiv7451746540ygrp-reco
#yiv7451746540reco-head {
color:#ff7900;font-weight:700;}

#yiv7451746540 #yiv7451746540ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv7451746540 #yiv7451746540ygrp-sponsor #yiv7451746540ov
li a {
font-size:130%;text-decoration:none;}

#yiv7451746540 #yiv7451746540ygrp-sponsor #yiv7451746540ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv7451746540 #yiv7451746540ygrp-sponsor #yiv7451746540ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv7451746540 #yiv7451746540ygrp-text {
font-family:Georgia;}

#yiv7451746540 #yiv7451746540ygrp-text p {
margin:0 0 1em 0;}

#yiv7451746540 #yiv7451746540ygrp-text tt {
font-size:120%;}

#yiv7451746540 #yiv7451746540ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv7451746540