How do I Rank each record in a query?

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

I have 2 fields in a query. One field is the [Company Name]. The other
field is the Total Sales amount for that company. I have this query sorted
in descending order by the Sales Amount. I would like to add a third field
that will be a calculated field that will determine the rank of that company
according to their Sales Amount. For Example:

Company Name Sales Amount Rank
ABC Co. $1,000 1
KMart $500 2
Ames $100 3

Can someone tell me how to create this calculating field?


Thanks,
Chris
 
Dear Chris:

SELECT [Company Name], [Sales Amount],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[Sales Amount] > Q.[SalesAmount]) AS Rank
FROM YourQuery Q
ORDER BY [Sales Amount] DESC

Replace YourQuery with the name of the query you already have, or
incorporate the above syntax in that query.
 
Tom,

What are Q1 and Q? I was not able to get this to work. Here is the actual
SQL that I am using (instead of Sales Amount, it is actually [MaxOfQuote
Extended Price].

SELECT [Quotes Totals - by Company].[MaxOfQuote Extended Price], [Quotes
Totals - by Company].[COMPANY NAME], (SELECT COUNT(*) + 1 FROM [Quotes
Totals - by Company]
WHERE [Quotes Totals - by Company].[MaxOfQuote Extended Price] >
[Quotes Totals - by Company].[MaxOfQuote Extended Price]) AS Rank
FROM [Quotes Totals - by Company]
ORDER BY [Quotes Totals - by Company].[MaxOfQuote Extended Price] DESC;

The query returns with a rank of 1 for each company.

Thanks,
Chris


Tom Ellison said:
Dear Chris:

SELECT [Company Name], [Sales Amount],
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.[Sales Amount] > Q.[SalesAmount]) AS Rank
FROM YourQuery Q
ORDER BY [Sales Amount] DESC

Replace YourQuery with the name of the query you already have, or
incorporate the above syntax in that query.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Nelson said:
I have 2 fields in a query. One field is the [Company Name]. The other
field is the Total Sales amount for that company. I have this query sorted
in descending order by the Sales Amount. I would like to add a third field
that will be a calculated field that will determine the rank of that company
according to their Sales Amount. For Example:

Company Name Sales Amount Rank
ABC Co. $1,000 1
KMart $500 2
Ames $100 3

Can someone tell me how to create this calculating field?


Thanks,
Chris
Click to expand...
Click to expand...
 
Thanks for the response. Updating w/the new code, it does work - however,
it takes about 1 minute to run (I don't have a slow computer either!) and
then my computer runs very very slow once it finally does come up (CPU
spikes at 99). I don't know if this is because of the SQL code being used
here or if it has something to do with the queries it is referencing.
Whatever the case, this solution is not acceptable due to the time it takes
to run - although I appreciate very much your help and welcome any more
suggestions.

For a little more explanation on the queries I am referencing:
Query1 (Quotes Totals) - souce tables are "Quote"
and "Quote Detail" which provide the Company Name and Qty and Unit Prices -
the query calculates the Total value of each quote using the following
calculated field - Quote Extended Price: Sum([UNIT PRICE]*[QTY]) - There are
23,525 records in this query.

Query2 (Quotes Totals - by Company) - source query is Query1 (Quotes
Totals) - this query provides the same info as above but only shows the
single largest Quote Total for each Company by using the "Max" function in
the "Totals" section of the Query Design grid - There are 9,469 records
returned by this query.

Query3 (Quotes Totals - Ranked) - this is the query that you just
helped me create and it is based on Query2 (Quotes Totals - by Company) and
your Q and Q1 alias tables.

I am trying to rank each company because I have a report that lists all our
customers and the price quotes we gave them. One customer usually has many
different price quotes that we have given them over the years. My report
also lists the Quote Total next to the Quote Number.

I want to be able to Group/Sort by showing all the quotes for the company
with the largest Quote Total first. Here is an example:

Company Quote # Quote Total
McDonalds 100 $999,999.00
McDonalds 12 $10,000.00
McDonalds 19 $5,000.00
McDonalds 112 $100.00
Wendys 17 $800,000.00
Wendys 51 $50,000.00
Burger King 23 $65,000.00
Popeyes 89 $42,000.00
Popeyes 200 $200.00


Anyone know how to do this? Can I do this straight from the report, or do I
have to build a special query like I have been trying to do above for this?



Thanks,
Chris


Tom Ellison said:
Dear Chris:

SELECT [MaxOfQuote Extended Price], [COMPANY NAME],
(SELECT COUNT(*) + 1 FROM [Quotes Totals - by Company] Q1
WHERE Q1.[MaxOfQuote Extended Price] > Q.[MaxOfQuote Extended Price])
AS Rank
FROM [Quotes Totals - by Company] Q
ORDER BY [MaxOfQuote Extended Price] DESC;

Q and Q1 are aliases. Since the query must independently reference the same
query twice, these are essential.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Nelson said:
Tom,

What are Q1 and Q? I was not able to get this to work. Here is the actual
SQL that I am using (instead of Sales Amount, it is actually [MaxOfQuote
Extended Price].

SELECT [Quotes Totals - by Company].[MaxOfQuote Extended Price], [Quotes
Totals - by Company].[COMPANY NAME], (SELECT COUNT(*) + 1 FROM [Quotes
Totals - by Company]
WHERE [Quotes Totals - by Company].[MaxOfQuote Extended Price] >
[Quotes Totals - by Company].[MaxOfQuote Extended Price]) AS Rank
FROM [Quotes Totals - by Company]
ORDER BY [Quotes Totals - by Company].[MaxOfQuote Extended Price] DESC;

The query returns with a rank of 1 for each company.

Thanks,
Chris
Click to expand...
Click to expand...
 
Dear David:

I have not made any such assumption. What you describe is exactly what I
expect it to produce (except that in this instance the rank of 1 is to be
applied to the largest, not the smallest value.

If the two largest values are the same, then the correct answer is that they
both have a Rank of 1, and the next largest has a rank of 3. That's the
natural and logical expectation. If you go to the horse races, that's the
way it works. Win, win, show.

I frequently show in this newsgroup how to break the tie using some other
column's values. But this is not always desirable. For that reason, it is
not correct to say that I have assumed the [MaxOfQuote Extended Price] is
unique. The query I provided works perfectly well whether it is unique or
not, unless it is desired to have another column's values break the tie.
Nothing to this effect was specified in the problem.
 
Back
Top