calculating a ranking

  • Thread starter Thread starter Mark McCoy
  • Start date Start date
M

Mark McCoy

I have this query below that I use to calculate the percent increase for
sales by manager, by region. I want to take the percentages and rank them
in order for the TOP 10. How do I do this based on the highest % Increase.

SELECT
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
 
Order by the % Increase in DESCending order and use the TOP operator. So
something like the untested SQL.

SELECT TOP 10
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
....
ORDER BY ([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) DESC
 
This solution will place them in the correct order. I need to take it one
step further. I need a calculated field (call it Sales Rank) that will
automatically define their ranking. as First, Second, Third etc...

That way, regardless of the order, or sort of the output, I will know that X
was our #1 sales mgr for the period and Y was #2 and Z was #10.

Mark

John Spencer (MVP) said:
Order by the % Increase in DESCending order and use the TOP operator. So
something like the untested SQL.

SELECT TOP 10
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
...
ORDER BY ([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) DESC

Mark said:
I have this query below that I use to calculate the percent increase for
sales by manager, by region. I want to take the percentages and rank them
in order for the TOP 10. How do I do this based on the highest % Increase.

SELECT
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
Click to expand...
Click to expand...
 
That is more complex and a lot slower. Hopefully Tom Ellison is monitoring this
thread. He can come up with a response a lot easier than I can. The basic
problem is that you would need to use a subquery to generate the ranking.

What version of Access are you using? Are you using Access plus Jet (an .mdb)
or Access plus MSDE (an .adb) or are you using SQL server with Access? The
solution would vary greatly depending on your answer.

Mark said:
This solution will place them in the correct order. I need to take it one
step further. I need a calculated field (call it Sales Rank) that will
automatically define their ranking. as First, Second, Third etc...

That way, regardless of the order, or sort of the output, I will know that X
was our #1 sales mgr for the period and Y was #2 and Z was #10.

Mark

John Spencer (MVP) said:
Order by the % Increase in DESCending order and use the TOP operator. So
something like the untested SQL.

SELECT TOP 10
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
...
ORDER BY ([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) DESC

Mark said:
I have this query below that I use to calculate the percent increase for
sales by manager, by region. I want to take the percentages and rank them
in order for the TOP 10. How do I do this based on the highest % Increase.

SELECT
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
Click to expand...
Click to expand...
Click to expand...
 
Dear Mark:

And thanks for the endorsement, John. Always good to hear from you!

OK, here's me thinking my way through this, out loud.

First, the query is obviously not complete as posted. Also, it
contains references to two tables not in evidence - no FROM clause.

But, based on what I can see here, I'll try to construct the ranking
based on the query shown rather than directly on the tables. That
works, too.

SELECT TOP 10 SMGR, REGION, SumOfYRDS_2004, SumOfSAME_YRDS_2003,
[% Increase],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[% Increase] > Q.[% Increase]) AS Rank
FROM YourQuery Q
ORDER BY [% Increase] DESC

Now, I've had to interpret some of what you said in what I thought was
a reasonable, but not necessarily correct fashion, especially what you
wanted for TOP 10. Are we at all close?

Now, I'd be glad to transmit what little I know about these subqueries
to John and others, but then I wuoldn't have a specialty of my own! A
guy has to have SOMETHING for which he's known!

But, when I'm needed in a particular thread, you MVPs could drop me a
line. I don't often spend enough time looking at threads that are
already underway to catch this. Just lucky this time. Well lucky if
you assume I've been helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Here's the complete query as written. query name: qrySales_Increase
I'm using MS Access (.mdb)

To reiterate, I need to define [RANK] as 1,2,3,4, etc.... by the [%
Increase] so I know who my top sales managers [SMR] and [REGION] are for a
given period.

SELECT [Yards for Cash Super Report].SMR, [Yards for Cash Super
Report].REGION, Sum([Major Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Major Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]-[SumOfSAME_YRDS_2003]) AS [Absolute Change],
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
FROM [Yards for Cash Super Report] LEFT JOIN [Major Super Report] ON [Yards
for Cash Super Report].MAJOR = [Major Super Report].MAJOR
GROUP BY [Yards for Cash Super Report].SMR, [Yards for Cash Super
Report].REGION;


I'm not sure I understand how to associate the qrySales_Increase with the
select statement provided.

SELECT TOP 10 SMGR, REGION, SumOfYRDS_2004, SumOfSAME_YRDS_2003,
[% Increase],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[% Increase] > Q.[% Increase]) AS Rank
FROM YourQuery Q


I appreciate all of this help. As a novice this type of assitence is
invaluable.

Thank You Tom and John.

Mark



Tom Ellison said:
Dear Mark:

And thanks for the endorsement, John. Always good to hear from you!

OK, here's me thinking my way through this, out loud.

First, the query is obviously not complete as posted. Also, it
contains references to two tables not in evidence - no FROM clause.

But, based on what I can see here, I'll try to construct the ranking
based on the query shown rather than directly on the tables. That
works, too.

SELECT TOP 10 SMGR, REGION, SumOfYRDS_2004, SumOfSAME_YRDS_2003,
[% Increase],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[% Increase] > Q.[% Increase]) AS Rank
FROM YourQuery Q
ORDER BY [% Increase] DESC

Now, I've had to interpret some of what you said in what I thought was
a reasonable, but not necessarily correct fashion, especially what you
wanted for TOP 10. Are we at all close?

Now, I'd be glad to transmit what little I know about these subqueries
to John and others, but then I wuoldn't have a specialty of my own! A
guy has to have SOMETHING for which he's known!

But, when I'm needed in a particular thread, you MVPs could drop me a
line. I don't often spend enough time looking at threads that are
already underway to catch this. Just lucky this time. Well lucky if
you assume I've been helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have this query below that I use to calculate the percent increase for
sales by manager, by region. I want to take the percentages and rank them
in order for the TOP 10. How do I do this based on the highest % Increase.

SELECT
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
Click to expand...
Click to expand...
 
Dear Mark:

The way I meant it was to replace "YourQuery" with the actual name of
your query. I didn't see the name of your query in the earlier
posting.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Here's the complete query as written. query name: qrySales_Increase
I'm using MS Access (.mdb)

To reiterate, I need to define [RANK] as 1,2,3,4, etc.... by the [%
Increase] so I know who my top sales managers [SMR] and [REGION] are for a
given period.

SELECT [Yards for Cash Super Report].SMR, [Yards for Cash Super
Report].REGION, Sum([Major Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Major Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]-[SumOfSAME_YRDS_2003]) AS [Absolute Change],
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
FROM [Yards for Cash Super Report] LEFT JOIN [Major Super Report] ON [Yards
for Cash Super Report].MAJOR = [Major Super Report].MAJOR
GROUP BY [Yards for Cash Super Report].SMR, [Yards for Cash Super
Report].REGION;


I'm not sure I understand how to associate the qrySales_Increase with the
select statement provided.

SELECT TOP 10 SMGR, REGION, SumOfYRDS_2004, SumOfSAME_YRDS_2003,
[% Increase],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[% Increase] > Q.[% Increase]) AS Rank
FROM YourQuery Q


I appreciate all of this help. As a novice this type of assitence is
invaluable.

Thank You Tom and John.

Mark



Tom Ellison said:
Dear Mark:

And thanks for the endorsement, John. Always good to hear from you!

OK, here's me thinking my way through this, out loud.

First, the query is obviously not complete as posted. Also, it
contains references to two tables not in evidence - no FROM clause.

But, based on what I can see here, I'll try to construct the ranking
based on the query shown rather than directly on the tables. That
works, too.

SELECT TOP 10 SMGR, REGION, SumOfYRDS_2004, SumOfSAME_YRDS_2003,
[% Increase],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[% Increase] > Q.[% Increase]) AS Rank
FROM YourQuery Q
ORDER BY [% Increase] DESC

Now, I've had to interpret some of what you said in what I thought was
a reasonable, but not necessarily correct fashion, especially what you
wanted for TOP 10. Are we at all close?

Now, I'd be glad to transmit what little I know about these subqueries
to John and others, but then I wuoldn't have a specialty of my own! A
guy has to have SOMETHING for which he's known!

But, when I'm needed in a particular thread, you MVPs could drop me a
line. I don't often spend enough time looking at threads that are
already underway to catch this. Just lucky this time. Well lucky if
you assume I've been helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have this query below that I use to calculate the percent increase for
sales by manager, by region. I want to take the percentages and rank them
in order for the TOP 10. How do I do this based on the highest % Increase.

SELECT
[Yards].SMGR,
[Yards].REGION,
Sum([Super Report].YRDS_2004) AS SumOfYRDS_2004,
Sum([Super Report].SAME_YRDS_2003) AS SumOfSAME_YRDS_2003,
([SumOfYRDS_2004]/[SumOfSAME_YRDS_2003]) AS [% Increase]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top