Rank order fields in record (Access)
Posted on 2009-04-10
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....