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

How to rank different fields in a query?

P: 11
I have a database that contains many number type fields.
I would like to compare the numbers in these fields within the same records and to rank them then put the rank number in new fields.

Thanks
Mar 24 '12 #1
Share this Question
Share on Google+
21 Replies


Expert 100+
P: 1,035
did you check previous answer on bytes.com ?
like this one: http://bytes.com/topic/access/answer...-rank-function

it explains gives all the info the get you going...
Mar 24 '12 #2

P: 11
That is different: I need the rank of the different fields within the same records (same row). Not within the same field (same column).
Mar 25 '12 #3

Expert 100+
P: 1,035
Can you give a (maybe simplified) example of what you have, and what the situation should be after applying this rank?
Mar 25 '12 #4

P: 11
Fs are the fields
FsR are the rank fields as I need
F1 F2 F3 F4 F5 F1R F2R F3R F4R F5R
22 7 3 15 10 1 4 5 2 3
Mar 25 '12 #5

Expert 100+
P: 1,035
Hmmm, i'm trying to solve this in MySQL, (i dont have/use access)

start with:
Expand|Select|Wrap|Line Numbers
  1. select * from rank_table;
  2. +---+------+------+------+------+------+
  3. | i | s1   | s2   | s3   | s4   | s5   |
  4. +---+------+------+------+------+------+
  5. | 1 |   22 |    7 |    3 |   15 |   10 |
  6. | 2 | 4217 |  919 | 1186 |  128 |  200 |
  7. | 3 | 2354 |  575 |  657 |   52 |   93 |
  8. | 4 |   82 |  479 |  677 |   75 |  136 |
  9. | 5 |   44 |  395 |  479 |   59 |   93 |
  10. +---+------+------+------+------+------+
  11.  
put values for every row in a column:
Expand|Select|Wrap|Line Numbers
  1. select i,s1 from rank_table 
  2.     union 
  3.     select i,s2 from rank_table 
  4.     union 
  5.     select i,s3 from rank_table 
  6.     union 
  7.     select i,s4 from rank_table 
  8.     union 
  9.     select i,s5 from rank_table
This will give you:
Expand|Select|Wrap|Line Numbers
  1. +---+------+
  2. | i | s1   |
  3. +---+------+
  4. | 1 |   22 | = first row, first column
  5. | 2 | 4217 |
  6. | 3 | 2354 | = third row, first column
  7. | 4 |   82 |
  8. | 5 |   44 |
  9. | 1 |    7 | = second row, second column
  10. | 2 |  919 |
  11. | 3 |  575 |
  12. | 4 |  479 |
  13. | 5 |  395 |
  14. | 1 |    3 |
  15. | 2 | 1186 |
  16. | 3 |  657 |
  17. | 4 |  677 | = fourth row, third column 
  18. ....
add the rownumber. I'm not sure how to do this in access, but MySQL has a little trick to do it:
Expand|Select|Wrap|Line Numbers
  1. set @a:=0;
  2. select @a:=CASE WHEN t.i=1 THEN @a+1 ELSE @a END a,t.i,t.s1 from 
  3.     (select i,s1 from rank_table 
  4.     union 
  5.     select i,s2 from rank_table 
  6.     union 
  7.     select i,s3 from rank_table 
  8.     union 
  9.     select i,s4 from rank_table 
  10.     union 
  11.     select i,s5 from rank_table ) t ;
output:
Expand|Select|Wrap|Line Numbers
  1. +------+---+------+
  2. | a    | i | s1   |
  3. +------+---+------+
  4. |    1 | 1 |   22 |
  5. |    1 | 2 | 4217 |
  6. |    1 | 3 | 2354 |
  7. |    1 | 4 |   82 |
  8. |    1 | 5 |   44 |
  9. |    2 | 1 |    7 |
  10. |    2 | 2 |  919 |
  11. |    2 | 3 |  575 |
  12. |    2 | 4 |  479 |
  13. |    2 | 5 |  395 |
  14. |    3 | 1 |    3 |
  15. |    3 | 2 | 1186 |
  16. |    3 | 3 |  657 |
  17. |    3 | 4 |  677 |
  18. ......
Last step, sort by second column, and descending on third:
Expand|Select|Wrap|Line Numbers
  1. set @a:=0;
  2. select a,i,s1 from (
  3.     select @a:=CASE WHEN t.i=1 THEN @a+1 ELSE @a END a,t.i,t.s1 from 
  4.         (select i,s1 from rank_table 
  5.         union 
  6.         select i,s2 from rank_table 
  7.         union 
  8.         select i,s3 from rank_table 
  9.         union 
  10.         select i,s4 from rank_table 
  11.         union 
  12.         select i,s5 from rank_table ) t ) u
  13. order by i, s1 desc;
results in:
Expand|Select|Wrap|Line Numbers
  1. +------+---+------+
  2. | a    | i | s1   |
  3. +------+---+------+
  4. |    1 | 1 |   22 |
  5. |    4 | 1 |   15 |
  6. |    5 | 1 |   10 |
  7. |    2 | 1 |    7 |
  8. |    3 | 1 |    3 |
  9. |    1 | 2 | 4217 |
  10. |    3 | 2 | 1186 |
  11. |    2 | 2 |  919 |
  12. |    5 | 2 |  200 |
  13. |    4 | 2 |  128 |
  14. |    1 | 3 | 2354 |
  15. |    3 | 3 |  657 |
  16. |    2 | 3 |  575 |
  17. ....
In column 'a' you will see your ranking... ;)

The simple answer is: YES it can be done, but if you store your numbers not in one row, but in more rows than it would be a lot easier to get this result.
Mar 25 '12 #6

P: 11
That is good.
However, I need the rank fields beside the original fields not on top of each others
Thanks a lot
Mar 25 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
I suspect the first thing you need to understand is about Database Normalisation and Table Structures. What you ask is confusing for database experts as they would never think along those lines. All I can suggest is that you do multiple comparisons of the data to determine which order they're in. A messy solution certainly, but it rather matches the question I'm afraid.

Even if you do decide to continue along this course and manage to get it working, your database will be a long way short of Normalised, which ought to worry you.
Mar 25 '12 #8

P: 11
Can we use combination of Switch, Max and Min functions to do it
Mar 26 '12 #9

NeoPa
Expert Mod 15k+
P: 31,494
Switch() may well be worth proceeding with. Min() and Max() are functions which aggregate across records, not across fields (So No. They will be entirely useless in this scenario - at least until you get a sensible design and hold the data across records instead of fields of course).
Mar 26 '12 #10

P: 11
I am interrested in the data across fields because my database contaiains now thousands of records and about 30 fields. If I reversed it the fields will be in thousands and the records only 30.
Mar 26 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
YasserGIT:
If I reversed it the fields will be in thousands and the records only 30.
Clearly that would be quite a ridiculous approach, but who mentioned anything about reversing it?
Mar 26 '12 #12

Rabbit
Expert Mod 10K+
P: 12,366
The way your data is, you have to use Luuk's approach.

1) Unpivot your data to normalize it.
2) Use a ranking query.
3) Repivot if you want to denormalize your data.

Acess doesn't have a row number so you won't be able to use Luuk's method of ranking. But you can achieve the same resutls using either a subquery in the select or by joining the source to itself.
Mar 26 '12 #13

P: 11
Excuse me, for being not very good in access.
I know that there is no built-in functions in access to compare values in records (it compares values in fields only).
I wonder if it is possible to invent a new function to do that.
I did a search and I found a function that compare numbers in records to turn the maximum and minimum.
Can any one do the same for ranking the numbers in records.

The function was like that:
Expand|Select|Wrap|Line Numbers
  1. Function Maximum(ParamArray FieldArray() As Variant)
  2. ' Declare the two local variables.
  3. Dim I As Integer
  4. Dim currentVal As Variant
  5.  
  6. ' Set the variable currentVal equal to the array of values.
  7. currentVal = FieldArray(0)
  8.  
  9. ' Cycle through each value from the row to find the largest.
  10. For I = 0 To UBound(FieldArray)
  11. If FieldArray(I) > currentVal Then
  12. currentVal = FieldArray(I)
  13. End If
  14. Next I
  15.  
  16. ' Return the maximum value found.
  17. Maximum = currentVal
  18.  
  19. End Function
  20. Function Minimum(ParamArray FieldArray() As Variant)
  21. ' Declare the two local variables.
  22. Dim I As Integer
  23. Dim currentVal As Variant
  24.  
  25. ' Set the variable currentVal equal to the array of values.
  26. currentVal = FieldArray(0)
  27.  
  28. ' Cycle through each value from the row to find the smallest.
  29. For I = 0 To UBound(FieldArray)
  30. If FieldArray(I) < currentVal Then
  31. currentVal = FieldArray(I)
  32. End If
  33. Next I
  34.  
  35. ' Return the minimum value found.
  36. Minimum = currentVal
Mar 26 '12 #14

Rabbit
Expert Mod 10K+
P: 12,366
Please use code tags when posting code.

Yes, you could use code. It would take longer to run. But it's up to you which approach you use, SQL or VBA.
Mar 26 '12 #15

P: 11
Thank you, I will do next time.
I am not very good enough to work with codes. May any of the kind experts do it for me please. My database is in access.
Mar 26 '12 #16

Rabbit
Expert Mod 10K+
P: 12,366
Sorry, I don't give out code. I can help by pointing out errors and help you fix your code but I rarely ever give out code. Do a tutorial, attempt one of the solutions, and post back here and I will be more than glad to help out.
Mar 26 '12 #17

P: 11
That is very sad because I am very new to codes. Any way, thanks very much for your help.
Mar 26 '12 #18

Rabbit
Expert Mod 10K+
P: 12,366
You may be new, but that's all the more reason to learn the basics. You can't expect people to hand you the answer every time. You need to do the work. We were all new once. And we all had to learn the basics. It's how you become an expert.
Mar 26 '12 #19

P: 11
I do not think that is fair. I have a problem now and I need help. Learning codes is not an easy task and needs a lot of time that unfotunately I do not have because of my job as a physician.
Mar 26 '12 #20

Rabbit
Expert Mod 10K+
P: 12,366
And I think it's unfair that you expect the work to be done for you.
Mar 26 '12 #21

P: 11
you may be right. thank you.
Mar 26 '12 #22

Post your reply

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