Nth Maximum Value (1 Viewer)

[Gmelsofty]

Hello all,
How can I find the nth maximum value in multiple fields?
For example how can I find the 2nd or 3rd or 4th largest value per record in the following sample set of data?
ID Name Field1 Field 2 Field3 Field4 Field5 Field6 2ndHigh 3rd High 4th
1 Abbyy 89 78 45 90 55 78 ---- ---- ---
2 Abbxx 65 62 55 34 98 30 --- ---- ---

Much Thanks.

 

[The_Doc_Man]

Start with a query that orders the records by the field in question.

Then you could write VBA to open said query and step from the first to the nth record.

The other alternative is to make the query above include a "TOP n" as part of the SELECT statement. Then you can write a query of a query - that reads the first query but in descending order of the critical field. That first record in the second query is the nth-largest of your records.

NOTE that if any of them are tied, you run into a conundrum about which one is really "nth" largest.

 

[plog]

ID Name Field1 Field 2 Field3 Field4 Field5 Field6 2ndHigh 3rd High 4th
1 Abbyy 89 78 45 90 55 78 ---- ---- ---
2 Abbxx 65 62 55 34 98 30 --- ---- --

That doesn't lend insight into your issue. For 1 its not a normalized set of data and for another, I have no idea how you are ordering all that data. What would be the 3rd highest value? Per row? Per field? Overall? Very confused.

Can you provide a better sample set? Give me 2 sets of data:

A. Starting data from your table. Include actual table and field names and enough data to cover all cases.

B. Expected results based on A. Show me the exact data you want returned.

 

[Gmelsofty]

Am interested in getting the nth highest value per row. Kindly help me.

 

[isladogs]

Please can you do what Plog has already requested:

That doesn't lend insight into your issue. For 1 its not a normalized set of data and for another, I have no idea how you are ordering all that data. What would be the 3rd highest value? Per row? Per field? Overall? Very confused.

Can you provide a better sample set? Give me 2 sets of data:

A. Starting data from your table. Include actual table and field names and enough data to cover all cases.

B. Expected results based on A. Show me the exact data you want returned.

That will allow for a more detailed response.

The data as supplied in post #1, looks more like a spreadsheet than an Access table

 

[The_Doc_Man]

Am interested in getting the nth highest value per row

This jumped out at me in the follow-up posts. Your problem is a non-normalized data set. Access doesn't work so well on malformed record structures. When you have a table that has field1, field2, field3, etc. as actual field names, you show us a table built from Excel and this is where you quickly find out that Access and Excel are two WILDLY different beasties despite their occasional visual similarity.

Read up on "database normalization" using the great Google brain or use the SEARCH feature of this forum for "normalization" articles. If your data sets were structured correctly, your solution would be as I described earlier. But (in Excel terms) using row-based criteria doesn't work in Access. It is column-oriented. (Actually, it is more technically RECORD oriented.)