Solved Is it possible to find and display the max value between a series of fields?

[ThatTransportationPerson]

Hi all!

I have a table with ~200 records and I need to compare 11 different fields to find which field has the largest value. Here's an example table:

State Name	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6	Value 7	Value 8	Value 9
California	20.22	94.2	74.0	75.9	33.33	67.4	60.5	92.1	14.6
Florida	81.1	48.6	72.1	57.5	52.4	79.9	12.7	16.8	36.3

The resulting table would look like this:

State Name	Max Value	Value Name
California	94.2	Value 2
Florida	81.1	Value 1

I'm struggling to think of a good way to do this. I would be able to do a simple comparison if there were only 3 or so columns, but with 11 I'm feeling a bit overwhelmed. Any ideas would be greatly appreciated. Thank you in advance!

 

[Gasman]

It is difficult, as your structure is all wrong.
If the structure was correct, it would be very simple.
Edit: You might be able to use these functions, but still need to pass 11 fields?

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

allenbrowne.com

 

[jdraw]

What do the Value 1 ... Value 11 represent?

 

[CJ_London]

or create a union query (called unionquery) to normalise the data

SELECT StateName, "Value1" AS vName, Value1 as V FROM myTable
UNION SELECT StateName, "Value2" AS vName, Value2 as V FROM myTable
UNION SELECT StateName, "Value3" AS vName, Value3 as V FROM myTable
etc

then a query (qry1) to find the max for each state

SELECT statename, max(V) as maxValue
FROM unionQuery
GROUP BY statename

finally to get the 'name' of the field/column

SELECT unionquery.statename, qry1.maxvalue, unionquery.vName
FROM unionquery INNER JOIN qry1 ON unionquery.statename=qry1.statename AND unionquery.V=qry1.maxValue

this all assumes there cannot be two max values for any state - your example doesn't show them and you haven't said what you would want to do in the eventuality that they could occur

 

[ThatTransportationPerson]

Hey jdraw,

They are ratings. I guess a better example would be the student scores in different subjects, and I want to find which subject each student excels at the most. Ex: Student A got a 55.4% in Math and a 78.2% in Science. So I'd highlight Science as Student A's top class.

What do the Value 1 ... Value 11 represent?

 

[jdraw]

Based on your analogy, then the more appropriate data structure would be Student-->StudentMarkInSubject<--Subject

 

[ThatTransportationPerson]

Based on your analogy, then the more appropriate data structure would be Student-->StudentMarkInSubject<--Subject

Yes, that would be ideal but unfortunately this is data coming from a third party. :/ I'm going to try CJ_London's suggestions and see how it turns out.

 

[jdraw]

Try CJ;s union query etc. Sounds good!

When I received "data" from a third party that was not in a format conducive to processing, we ran that data through a intermediate process to reformat the data to normalized form. Then, ran the normalized data through the "intended process".

If this is a recurring issue, you could set up something to streamline things.

 

[CJ_London]

I'm going to try CJ_London's suggestions and see how it turns out.

since this is marking, I guess it won't matter if there are two max marks with the same value.

Note that using the union query is a patch - if a new subject is added, you will need to modify the union query. Would be better to normalise the data as it is received per jdraw's suggestion

 

[arnelgp]

you create a function that will Return the MaxValue and the Field name where it is found.
see Module1 for the function fnMax().
Open Query1 and in design view see how the function is called from this query.

 

[GPGeorge]

Hi all!

I have a table with ~200 records and I need to compare 11 different fields to find which field has the largest value. Here's an example table:

Spoiler: etc State Name

Spoiler: etc

[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD]Value 5[/TD]
[TD]Value 6[/TD]
[TD]Value 7[/TD]
[TD]Value 8[/TD]
[TD]Value 9[/TD]

Spoiler: etc

[TR]
[TD]California[/TD]
[TD]20.22[/TD]
[TD]94.2[/TD]
[TD]74.0[/TD]
[TD]75.9[/TD]
[TD]33.33[/TD]
[TD]67.4[/TD]
[TD]60.5[/TD]
[TD]92.1[/TD]
[TD]14.6[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[TD]81.1[/TD]
[TD]48.6[/TD]
[TD]72.1[/TD]
[TD]57.5[/TD]
[TD]52.4[/TD]
[TD]79.9[/TD]
[TD]12.7[/TD]
[TD]16.8[/TD]
[TD]36.3[/TD]
[/TR]

Spoiler: etc

The resulting table would look like this:

State Name	Max Value	Value Name
California	94.2	Value 2
Florida	81.1	Value 1

I'm struggling to think of a good way to do this. I would be able to do a simple comparison if there were only 3 or so columns, but with 11 I'm feeling a bit overwhelmed. Any ideas would be greatly appreciated. Thank you in advance!

Perhaps you could correct this table design now and save a lot of trouble, such as trying to create this particular query, going forward. I realize that sometimes we get so far into a project that revising "spreadsheet" style tables to proper relational database tables is a significant undertaking and that in some situations, the effort seems overwhelming. However, in the long run, proper normalization is fundamental to a successful relational database application and it is well worth considering at any time.