Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Adding Rank to a table

Hey everyone,

Can someone look at the attached DB and tell me how to add a rank to an additional field?

I started with a query and had my data sorted the way I needed it.  I coudn't figure out how to add a rank to the query.  The table is my data as I queried it.

Thanks for any suggestion.

MC
Ranking.accdb
0
MCaliebe
Asked:
MCaliebe
  • 6
  • 3
  • 3
  • +1
2 Solutions
 
jerryb30Commented:
What do you mean by rank? Number sold? Number sold by Item?
0
 
MCaliebeAuthor Commented:
Rank the item number by the Total Sales
Higest sales value is 1, next value is 2, ect.
0
 
MCaliebeAuthor Commented:
In Excel, I would normally sort by the sales volume and add a column for Rank, and copy down 1~ however many records exist.  This isn't as easy in Access.  

I have to pull a number of queries on sales by product code and rank the products by total sales volume so this is going to have to be repeated.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
jerryb30Commented:
So pr_cd will always be the same or a given item_number?
0
 
jerryb30Commented:
And the combination of pr_cd is unique?
SELECT a.PR_CD, a.ITEM_NUMBER, a.Qty_Sold_Total, DCount("*","rank","qty_sold_total > " & [a].[qty_sold_total])+1 AS seq
FROM Rank AS a;
0
 
MCaliebeAuthor Commented:
Actually, I have multiple product codes PR_CD in my large table.  I have about 15000 recodes with 17 product codes.  

For each product code, I need to rank the Item_Numbers (Unique) by sales dollars.  I attached the DB again with a larger sampling of data.

Once I go through this exercise, I need to rank the items again by Qty_Sold, however I assume it will be using the same techniques I learn from this inquiry.

Thanks for your help.
Ranking.accdb
0
 
Jeffrey CoachmanCommented:
What about Ties?

The highest value is 200, but two records are 200?
0
 
Rey Obrero (Capricorn1)Commented:
see how to do this from this link

How to Rank Records Within a Query
http://support.microsoft.com/kb/208946
0
 
MCaliebeAuthor Commented:
I tried to use that method for adding a rank and what I believe was happening was when I queried the data, I got skips in my ranks ie. 1,2,7,21,35,37 ect. I believe I was ranking my entire source table, However as I placed criteria for the pr_cd, "STF", the balance was filtered out producing the inconsistent ranking.

As related to ties, I can leave them as duplicates and sort by item number...simply because I wouldn't have any further data to differentiate the ties.
0
 
Rey Obrero (Capricorn1)Commented:
try this query

SELECT tbl_sample_data.PR_CD, tbl_sample_data.ITEM_NUMBER, tbl_sample_data.Sales_Total, (select count(*) from tbl_sample_data T where [pr_cd]=tbl_sample_data.pr_cd and [Sales_Total]>=tbl_sample_data.[Sales_Total]) AS Ranking
FROM tbl_sample_data
WHERE (((tbl_sample_data.PR_CD)="STF"))
ORDER BY tbl_sample_data.Sales_Total DESC;

or this one


SELECT tbl_sample_data.PR_CD, tbl_sample_data.ITEM_NUMBER, tbl_sample_data.Qty_Sold_Total, (select count(*) from tbl_sample_data T where [pr_cd]=tbl_sample_data.pr_cd and [Qty_Sold_Total]>=tbl_sample_data.[Qty_Sold_Total]) AS Ranking
FROM tbl_sample_data
WHERE (((tbl_sample_data.PR_CD)="STF"))
ORDER BY tbl_sample_data.Qty_Sold_Total DESC;
0
 
MCaliebeAuthor Commented:
Cap,

Your suggestions work excellent.  Can I ask you do one more thing for me...a quick explaination of what your query is doing?

I believe I understand everything but this line

(select count(*) from tbl_sample_data T where [pr_cd]=tbl_sample_data.pr_cd and [Sales_Total]>=tbl_sample_data.[Sales_Total]) AS Ranking

What is T?
...and [Sales_Total]>=... does this refer to the field in the current query?
0
 
Rey Obrero (Capricorn1)Commented:
T is just an alias of the table created in memory as a copy of tbl_sample_data.. you can write the line this way too, sorry i did the shortcut

(select count(*) from tbl_sample_data T where T.[pr_cd]=tbl_sample_data.pr_cd and T.[Sales_Total]>=tbl_sample_data.[Sales_Total]) AS Ranking
0
 
MCaliebeAuthor Commented:
Thanks for taking the time to teach...  Works perfect.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now