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
21 3535
That is different: I need the rank of the different fields within the same records (same row). Not within the same field (same column).
Luuk 1,047
Expert 1GB
Can you give a (maybe simplified) example of what you have, and what the situation should be after applying this rank?
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
Luuk 1,047
Expert 1GB
Hmmm, i'm trying to solve this in MySQL, (i dont have/use access)
start with: -
select * from rank_table;
-
+---+------+------+------+------+------+
-
| i | s1 | s2 | s3 | s4 | s5 |
-
+---+------+------+------+------+------+
-
| 1 | 22 | 7 | 3 | 15 | 10 |
-
| 2 | 4217 | 919 | 1186 | 128 | 200 |
-
| 3 | 2354 | 575 | 657 | 52 | 93 |
-
| 4 | 82 | 479 | 677 | 75 | 136 |
-
| 5 | 44 | 395 | 479 | 59 | 93 |
-
+---+------+------+------+------+------+
-
put values for every row in a column: - select i,s1 from rank_table
-
union
-
select i,s2 from rank_table
-
union
-
select i,s3 from rank_table
-
union
-
select i,s4 from rank_table
-
union
-
select i,s5 from rank_table
This will give you: - +---+------+
-
| i | s1 |
-
+---+------+
-
| 1 | 22 | = first row, first column
-
| 2 | 4217 |
-
| 3 | 2354 | = third row, first column
-
| 4 | 82 |
-
| 5 | 44 |
-
| 1 | 7 | = second row, second column
-
| 2 | 919 |
-
| 3 | 575 |
-
| 4 | 479 |
-
| 5 | 395 |
-
| 1 | 3 |
-
| 2 | 1186 |
-
| 3 | 657 |
-
| 4 | 677 | = fourth row, third column
-
....
add the rownumber. I'm not sure how to do this in access, but MySQL has a little trick to do it: - set @a:=0;
-
select @a:=CASE WHEN t.i=1 THEN @a+1 ELSE @a END a,t.i,t.s1 from
-
(select i,s1 from rank_table
-
union
-
select i,s2 from rank_table
-
union
-
select i,s3 from rank_table
-
union
-
select i,s4 from rank_table
-
union
-
select i,s5 from rank_table ) t ;
output: - +------+---+------+
-
| a | i | s1 |
-
+------+---+------+
-
| 1 | 1 | 22 |
-
| 1 | 2 | 4217 |
-
| 1 | 3 | 2354 |
-
| 1 | 4 | 82 |
-
| 1 | 5 | 44 |
-
| 2 | 1 | 7 |
-
| 2 | 2 | 919 |
-
| 2 | 3 | 575 |
-
| 2 | 4 | 479 |
-
| 2 | 5 | 395 |
-
| 3 | 1 | 3 |
-
| 3 | 2 | 1186 |
-
| 3 | 3 | 657 |
-
| 3 | 4 | 677 |
-
......
Last step, sort by second column, and descending on third: - set @a:=0;
-
select a,i,s1 from (
-
select @a:=CASE WHEN t.i=1 THEN @a+1 ELSE @a END a,t.i,t.s1 from
-
(select i,s1 from rank_table
-
union
-
select i,s2 from rank_table
-
union
-
select i,s3 from rank_table
-
union
-
select i,s4 from rank_table
-
union
-
select i,s5 from rank_table ) t ) u
-
order by i, s1 desc;
results in: - +------+---+------+
-
| a | i | s1 |
-
+------+---+------+
-
| 1 | 1 | 22 |
-
| 4 | 1 | 15 |
-
| 5 | 1 | 10 |
-
| 2 | 1 | 7 |
-
| 3 | 1 | 3 |
-
| 1 | 2 | 4217 |
-
| 3 | 2 | 1186 |
-
| 2 | 2 | 919 |
-
| 5 | 2 | 200 |
-
| 4 | 2 | 128 |
-
| 1 | 3 | 2354 |
-
| 3 | 3 | 657 |
-
| 2 | 3 | 575 |
-
....
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.
That is good.
However, I need the rank fields beside the original fields not on top of each others
Thanks a lot
NeoPa 32,556
Expert Mod 16PB
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.
Can we use combination of Switch, Max and Min functions to do it
NeoPa 32,556
Expert Mod 16PB 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).
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.
NeoPa 32,556
Expert Mod 16PB 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?
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.
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: - Function Maximum(ParamArray FieldArray() As Variant)
-
' Declare the two local variables.
-
Dim I As Integer
-
Dim currentVal As Variant
-
-
' Set the variable currentVal equal to the array of values.
-
currentVal = FieldArray(0)
-
-
' Cycle through each value from the row to find the largest.
-
For I = 0 To UBound(FieldArray)
-
If FieldArray(I) > currentVal Then
-
currentVal = FieldArray(I)
-
End If
-
Next I
-
-
' Return the maximum value found.
-
Maximum = currentVal
-
-
End Function
-
Function Minimum(ParamArray FieldArray() As Variant)
-
' Declare the two local variables.
-
Dim I As Integer
-
Dim currentVal As Variant
-
-
' Set the variable currentVal equal to the array of values.
-
currentVal = FieldArray(0)
-
-
' Cycle through each value from the row to find the smallest.
-
For I = 0 To UBound(FieldArray)
-
If FieldArray(I) < currentVal Then
-
currentVal = FieldArray(I)
-
End If
-
Next I
-
-
' Return the minimum value found.
-
Minimum = currentVal
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.
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.
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.
That is very sad because I am very new to codes. Any way, thanks very much for your help.
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.
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.
And I think it's unfair that you expect the work to be done for you.
you may be right. thank you.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rado |
last post by:
Hi All,
This might quite simple process for some but I am finding it really
difficult to do. What is required is not a standard Duplicate query but a
variation on it. For example I have...
|
by: I_was_here |
last post by:
Hey if anyone is a query pro please showoff some knowledge thx.
Ie: I have a table with :
part
price
location
qty
1 part repeats throughout the table and its price remains the same but
it...
|
by: Branden |
last post by:
hi guys,
i was wondering if it is possible to extract selected words in a
field to be put in different fields automatically. Do i have to write
the code in vb?
This is what im trying to do....
|
by: Macbane |
last post by:
Hi,
I really hope you can help. I have a databse which records
interventions in patients drug therapy. Each record in the main table
contains 3 fields to capture the 3 main drug names involved...
|
by: shaqattack1992-newsgroups |
last post by:
Hello Everyone,
At work, employees have been using a spreadsheet that I am trying to
import into an access database to make some reports. The problem I'm
having is that in the spreadsheet, they...
|
by: iht |
last post by:
Fairly new to Access and really doesn't know much about SQL except some basic commands. Currently I'm working with a database that I've already sorted by some queries, and here's a simplified...
|
by: Bob |
last post by:
Hi all,
I have two tables that have the same type of indexes and I want to
include all records from both tables in the query once (even if the
indexes do not match). So basically I want to do...
|
by: anubha12 |
last post by:
hi ALL,
i want a different types query for practice the sql server.i am just start learning a sql server..so suggest to me a some websites where the query is available..
thnax in advanced
|
by: adamjblakey |
last post by:
Hi,
I have a function here:
var AdminResponse = "";
function parseResponse(){
var nText = AdminResponse.getElementsByTagName('optionText');
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |