Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding Rank to a table

Posted on 2013-01-08
13
Medium Priority
?
285 Views
Last Modified: 2013-01-09
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
Comment
Question by:MCaliebe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38757631
What do you mean by rank? Number sold? Number sold by Item?
0
 

Author Comment

by:MCaliebe
ID: 38757660
Rank the item number by the Total Sales
Higest sales value is 1, next value is 2, ect.
0
 

Author Comment

by:MCaliebe
ID: 38757663
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 26

Expert Comment

by:jerryb30
ID: 38757667
So pr_cd will always be the same or a given item_number?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38757675
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
 

Author Comment

by:MCaliebe
ID: 38757708
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38757721
What about Ties?

The highest value is 200, but two records are 200?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38757731
see how to do this from this link

How to Rank Records Within a Query
http://support.microsoft.com/kb/208946
0
 

Author Comment

by:MCaliebe
ID: 38757759
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38757778
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
 

Author Comment

by:MCaliebe
ID: 38758736
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38758826
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
 

Author Closing Comment

by:MCaliebe
ID: 38758857
Thanks for taking the time to teach...  Works perfect.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question