By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,945 Members | 1,342 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,945 IT Pros & Developers. It's quick & easy.

Ranking Query between 4 groups of data

P: 9
I have five Business Units; BU1, BU2, BU3, BU4, and BU5 is ALL four BU's (scored values are totalled across all BU's).

Each Business Unit has the same suppliers with individual monthly score data.

I want the ranking to be seperated between each Business Unit; BU1, BU2, BU3, BU4, and BU5.

When I use the following rank code, my ranking starts at 1 and goes through 60 becauase there are five seperate Business Units being queried.

Month Ranking Code:
Expand|Select|Wrap|Line Numbers
  1. Month Rank: (Select 1+ Count(*) from qrySelect_Metrics_01_PlacementsPerOrder_BySup_MonthScores Where [Month Score] > [qrySelect_Metrics_01_PlacementsPerOrder_BySup_MonthScores_01_1].[Month Score];)
YTD Ranking Code:
Expand|Select|Wrap|Line Numbers
  1. YTD Rank: (Select 1+ Count(*) from qrySelect_Metrics_01_PlacementsPerOrder_BySup_YTDScores Where [Data] > [qrySelect_Metrics_01_PlacementsPerOrder_BySup_YTDScores_01_1].[Data];)
I can create individual ranking queries then create a Union query to join all the data together. However, I have individual month ranking (1-12), ytd ranking by individual month (1-12), for 18 different metrics I am reporting on, for each of the 5 BU's. This is a LOT of individual queries to create.

Any assistance would be GREATLY appreciated!!!

Feb 8 '14 #1
Share this Question
Share on Google+
7 Replies

Expert Mod 15k+
P: 31,769
Where's the question in all this Chip?
Feb 8 '14 #2

P: 9
I thought the question was fairly straight forward. I hope this makes a little more sense now.

I need this type of ranking:
BU1 Supplier1 Score 28 Rank1
BU1 Supplier2 Score 22 Rank2

BU2 Supplier1 Score 35 Rank1
BU2 Supplier2 Score 27 Rank2
BU2 Supplier3 Score 27 Rank2
BU2 Supplier4 Score 22 Rank4

Not this type of ranking:
BU1 Supplier1 Score 28 Rank2
BU1 Supplier2 Score 22 Rank5

BU2 Supplier1 Score 35 Rank1
BU2 Supplier2 Score 27 Rank3
BU2 Supplier3 Score 27 Rank3


Feb 8 '14 #3

P: 294
So - by Business Unit and Supplier, you are trying to rank the 'Scores' by count [per month and ultimately per year] ?

Also, did you ever think of creating a temp table for all of this data? If you're bringing THAT much data back (you made it sound like a lot) then why rely on so many queries?

With the things you have provided I am tempted to suggest you append all of the correct data into a temp table and use VBA to do the work for you.

This way, you can open the recordset and compare each row of data. You can select the Max count of score for each business unit and assign them ranks, programmatically.

I could be totally off here, but maybe it will help.

Update: I was thinking something along the lines of..
Expand|Select|Wrap|Line Numbers
  1. Dim ct As Integer
  2. Dim mx As Integer
  3. Dim BU As String (Or Integer if it is a number)
  4. Dim rank As Integer
  6. Set Db
  7. Open the RecordSet
  9. BU = BusinessUnitField
  10. rank = RankField
  11. ct = CountField
  13. If rs.BOF And rs.EOF Then
  14. 'Do nothing
  15. Else
  16.     Do
  17.         If BU = 1  and rank < 1 Then
  18.             mx = DMax("ct", "YourTempTable", "rank Is Null")
  19.             rank = 1
  21.                Elseif BU = 1 And rank Is Null Then
  22.                mx = DMax("ct", "YourTempTable", "rank Is Null")
  23.                rank = DMax("rank", "YourTempTable", "rank Is Not Null") + 1
I am not 100% sure how your code works, and I am merely going off of assumption as to hopefully light a spark to get you going. I am just trying to show how to capture some of the data and possibly manipulate it.
Feb 8 '14 #4

P: 9
I would like to try this function you provided. However, your coding stopped at the end of line 23. Can you provide the rest of it please?

I am actually creating several temp tables then using those temp tables to populate another table with the collected data. I am not much of a vba programmer; I can read code, follow it and somewhat understand what is being done, then make some "tweaks" to make any changes I need. However, coding from scratch.....I have not had any classes and at most times I am at a total loss.

Thanks for your patience and support.

Feb 8 '14 #5

P: 294
Hi Chip,

I am actually unsure of how else to code because you need to provide more information. I merely thought that my code would be thought-provoking and you would be able to run with the ideas within it.

That being said, when you say things like: "I am actually creating several temp tables then using those temp tables to populate another table with the collected data" we become confused. You do not reference what data the temp tables are holding in reference to your question and your initial data set "example."

I am also fairly new (6 months) to programming, so before I go further with the code and try to give you a working solution I would most definitely suggest that a Mod sign off on my idea because they know 10x more about this than I would. They could tell me that my solution is useless (and it very well may be) and there's a better route.

Please try to clear up the "unknowns" as if you were a stranger reading what you said without ANY knowledge of your current system.

Hope this helps.
Feb 8 '14 #6

Expert Mod 15k+
P: 31,769
Mark, I cannot judge your contribution without understanding the question. Unfortunately, Chip still seems to feel their question is straightforward. I believe it hasn't even been expressed yet. I'm sure we'll agree to differ on that one.

You and I both know two things :
  1. Expressing a difficulty as a question that makes sense in and of itself is actually quite a hard thing to do - even though people generally fail to appreciate this - or even when that has even been achieved.
  2. A properly asked question can make all the difference to the quality of the answers posted. If you know what you're answering then you know what to include in your post. Many of the answers posted suffer from the lack of a decently asked question (And let me make it clear that in many cases this is much more down to the difficulty of asking than it is lack of effort).

Therefore, I will keep an eye out on this thread, but without knowing what precisely is being requested I cannot comment on how closely your answer fits the bill. Nevertheless, your style, where you post code in order that the OP (Chip in this case) can read it and try to work with it going forward, is certainly the way to go. We don't encourage any of our experts simply to do things for the members. That would be against the ethos of the whole site and most of us involved with it.

Chip. Believe me when I explain that I do understand how confusing it can be for someone in your shoes, new to the whole process. If I can offer one piece of advice to help you get the most of your experience here it would be to break down your questions into the smallest packets you can and ask them each individually and pay very careful attention to the wording. Mark makes the point that we are seeing your whole project and everything to do with it through your words. Anything not explained explicitly there will be unknown to your audience.
Feb 9 '14 #7

Expert Mod 10K+
P: 12,430
See if this article helps:
Feb 9 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.