MCaliebe
asked on
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
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
What do you mean by rank? Number sold? Number sold by Item?
ASKER
Rank the item number by the Total Sales
Higest sales value is 1, next value is 2, ect.
Higest sales value is 1, next value is 2, ect.
ASKER
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.
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.
So pr_cd will always be the same or a given item_number?
And the combination of pr_cd is unique?
SELECT a.PR_CD, a.ITEM_NUMBER, a.Qty_Sold_Total, DCount("*","rank","qty_sol d_total > " & [a].[qty_sold_total])+1 AS seq
FROM Rank AS a;
SELECT a.PR_CD, a.ITEM_NUMBER, a.Qty_Sold_Total, DCount("*","rank","qty_sol
FROM Rank AS a;
ASKER
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
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
What about Ties?
The highest value is 200, but two records are 200?
The highest value is 200, but two records are 200?
see how to do this from this link
How to Rank Records Within a Query
http://support.microsoft.com/kb/208946
How to Rank Records Within a Query
http://support.microsoft.com/kb/208946
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.[Sale s_Total]) AS Ranking
What is T?
...and [Sales_Total]>=... does this refer to the field in the current query?
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
What is T?
...and [Sales_Total]>=... does this refer to the field in the current query?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for taking the time to teach... Works perfect.