Using VBA to find nth values in an array

[JavaJava75]

thread707-1496731

Hi,

I'm working on a database that has 22 fields per record. I'd like to be able to find the top five fields (highest values) in each record and write VBA code to hold those values (and their field names). It would look like this:

Reason: A (11), B (2), C (5), D (80), E (30), F (0)....

VBA to return the below:
Top three reasons are D (80), E (30), A(11).

I followed the thread above (thread707-1496731) but I'm not sure how to grab the column name for that field in Maximum and Max2nd, and how to handle for 3rd, 4th, etc values.

Thanks in advance for any tips you can share with me!

 

[SkipVought]

Hi,

What application are you coding in ?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JavaJava75]

Microsoft Access 2010. Can't believe I forgot to mention that!

 

[SkipVought]

Then how are you accessing the data in the Access table?

I'd assume a Recordset. So why an array? Whatever, the array index can be used to get the Name property of the Recordset object.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JavaJava75]

My first thought was to try and create a function that I could run in the Query (almost like a calculated expression). I know how to find the maximum value out of the list, but I'm unable to find the next largest and the next largest, and include its column name (so I know where the highest value came from). My SQL knowledge isn't very strong.

 

[MajP]

This will take a little cut and paste, but I would do it all in sql. You need to normalize your data using a long union query, and then a top N by group query.

so qryNormal would look something like this assuming each row has an ID field called ID. Once you get the first union to work then it is a simple cut and paste for the remaining ones.

Select
 ID,
 "A" as FieldName,
 A as FieldValue
From
 YourTable
Union ALL
Select
  ID,
 "B" as FieldName,
  B as FieldValue
From 
  YourTable
...
Union All
Select
 ID,
  W as FieldName,
  W as FieldValue
From
  YourTable

This would give you data that looks like

ID FieldName FieldValue
1 A 11
1 B 2
1 C 5
1 D 80
....
1 W 99
2 A 6
2 B 8
....
99 A 3
....

Now you can get the top 5 per group. There are several sql variants for this

http://www.sql-ex.com/help/select16.php