CThomp2005
asked on
Rank order fields in record (Access)
Hello, everyone. Yet another Access/DAO/data question for the masses....
I need to determine rank-order data in a number of fields within a specific record (Access 2003 database). Actually, I need to find the top 3 highest values within these fields. Would be easy if these values were in columns (could pull "TOP 3"), but as they fall within fields within the record...how?
I'm actually pulling this data out of Access into an Excel spreadsheet using DAO. I've been toying around with some methods, but they end up long and drawn out. I have current written a Sub in Access to "transpose" these fields into rows in a "work" table to pull what I need. Example of the table I have:
VAR MR1 MR2 MR3 MR4 .... MR12 (column headings)
TOT 25 15 30 10 3
BUS 10 15 30 40
LOC 5 17 33 12
(this is only a small section of the table, there are other fields around these, but these are the ones I need to work with - their will be many more lines)
How, if possible, in "memory" (a recordset object, array, or some such) can I determine the highest three scores and, if possible, the columns/fields they are in "MR#" ? The results will be used to color-shade the corresponding cells in the Excel spreadsheet.
The Sub I've written spits out a "work" table that looks like:
LineNum TOT BUS LOC (col headings/field names)
1 25 10 5
2 15 17
3 30 15 33
4 10 30
...
12 3 40 12
...so this way, I"m assuming, I can pull the "TOP 3" using SQL in a DAO recordset definition to get what I need (any way to also pull the LineNumber along with the TOP 3 scores?).
Would be great if this can all be done with the recordset object or an array w/o having to resort to another table...
Thank everyone, for any ideas....
I need to determine rank-order data in a number of fields within a specific record (Access 2003 database). Actually, I need to find the top 3 highest values within these fields. Would be easy if these values were in columns (could pull "TOP 3"), but as they fall within fields within the record...how?
I'm actually pulling this data out of Access into an Excel spreadsheet using DAO. I've been toying around with some methods, but they end up long and drawn out. I have current written a Sub in Access to "transpose" these fields into rows in a "work" table to pull what I need. Example of the table I have:
VAR MR1 MR2 MR3 MR4 .... MR12 (column headings)
TOT 25 15 30 10 3
BUS 10 15 30 40
LOC 5 17 33 12
(this is only a small section of the table, there are other fields around these, but these are the ones I need to work with - their will be many more lines)
How, if possible, in "memory" (a recordset object, array, or some such) can I determine the highest three scores and, if possible, the columns/fields they are in "MR#" ? The results will be used to color-shade the corresponding cells in the Excel spreadsheet.
The Sub I've written spits out a "work" table that looks like:
LineNum TOT BUS LOC (col headings/field names)
1 25 10 5
2 15 17
3 30 15 33
4 10 30
...
12 3 40 12
...so this way, I"m assuming, I can pull the "TOP 3" using SQL in a DAO recordset definition to get what I need (any way to also pull the LineNumber along with the TOP 3 scores?).
Would be great if this can all be done with the recordset object or an array w/o having to resort to another table...
Thank everyone, for any ideas....
ASKER
Sorry, capricorn1, I guess I didn't explain that clearly...
For each original row/record, the highest three values of all the MR fields (1-12)... So, for the original TOT line, I would get back "30" "25" and "15". Preferably, I could also get the fields they're in ("3" "1" "2" or even "MR3" "MR1" "MR2"). Ordering of the returned top 3 is not necessary, just need to find the highest 3 values.
I have a sense this is "simple", but just can't get my head wrapped around it. I'm overthinking it, I think..
For each original row/record, the highest three values of all the MR fields (1-12)... So, for the original TOT line, I would get back "30" "25" and "15". Preferably, I could also get the fields they're in ("3" "1" "2" or even "MR3" "MR1" "MR2"). Ordering of the returned top 3 is not necessary, just need to find the highest 3 values.
I have a sense this is "simple", but just can't get my head wrapped around it. I'm overthinking it, I think..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks much for the response, capricorn1....
A couple of questions... How could I go about doing this without using the work table? Concern (which I've run into before) is that the data gets updated periodically, and if I forget to run the Sub to recreate the work table, I deal with old data. Any way to do this directly in a rs or array?
Next... I see the sql you threw out - along my same lines of thinking, except that I'm not horribly familiar with the "top 3" clause (know about it - never actually used it). By doing "top 3" against both LineNum and Tot, will that (in some way) pull a "top 3" for LineNum, also? Just curious here, what would (small example, if poss) would the resultant recordset/array look like after this (before it hits the "order by")?
Thanks very much. I'll be keeping tabs on this thread pretty closely as I'm at this point in my programming...so responses should be quick...
A couple of questions... How could I go about doing this without using the work table? Concern (which I've run into before) is that the data gets updated periodically, and if I forget to run the Sub to recreate the work table, I deal with old data. Any way to do this directly in a rs or array?
Next... I see the sql you threw out - along my same lines of thinking, except that I'm not horribly familiar with the "top 3" clause (know about it - never actually used it). By doing "top 3" against both LineNum and Tot, will that (in some way) pull a "top 3" for LineNum, also? Just curious here, what would (small example, if poss) would the resultant recordset/array look like after this (before it hits the "order by")?
Thanks very much. I'll be keeping tabs on this thread pretty closely as I'm at this point in my programming...so responses should be quick...
you have to do this using VBA codes in a user define function,
create a function, something like this
function getMax(paramarray args()) as double
dim vMax as double, j
vMax=args(0)
for j=1 to ubound(args)
if vMax < args(j) then
vMax=args(j)
end if
next
getmax=vMax
end function
to use in a query
select VAR,getMax([MR1],[MR2],...
that will get you the max value for each row
just tweak it to get three values
create a function, something like this
function getMax(paramarray args()) as double
dim vMax as double, j
vMax=args(0)
for j=1 to ubound(args)
if vMax < args(j) then
vMax=args(j)
end if
next
getmax=vMax
end function
to use in a query
select VAR,getMax([MR1],[MR2],...
that will get you the max value for each row
just tweak it to get three values
ASKER
OK...I understand where you're going with this... I get the "select...". What I'm not sure about is how to "tweak it to get three values"... How would I tweak to get the next two values (2nd and 3rd) ?
Sorry to be dense about this... Kinda thought there was a way to just read into an array and sort the array descending, and pull out the first three elements...or same with a rs...
Sorry to be dense about this... Kinda thought there was a way to just read into an array and sort the array descending, and pull out the first three elements...or same with a rs...
ASKER
Moving on...
After playing around on my end, I've decided to stick with the "use work table and pull top 3 via SQL" trick in your first response...
A slightly off-topic question, considering the solution I'm going with... From Excel VBA, how can I "run" the Sub that creates the work table? Can it be done without having the db open? Theory being, if I can run the code to create the table prior to pulling the top3 lines out, I know I'll be working with the most current data (in the work table).
Thanks again.
After playing around on my end, I've decided to stick with the "use work table and pull top 3 via SQL" trick in your first response...
A slightly off-topic question, considering the solution I'm going with... From Excel VBA, how can I "run" the Sub that creates the work table? Can it be done without having the db open? Theory being, if I can run the code to create the table prior to pulling the top3 lines out, I know I'll be working with the most current data (in the work table).
Thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
from your original table, which are the TOP 3?