Minggu, 17 April 2016

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














__._,_.___

Posted by: Adeboyejo Oyenuga <aoye_99@yahoo.co.uk>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (16)

.

__,_._,___

Tidak ada komentar:

Posting Komentar