MIN Problem

[chrisaroundtown]

I am trying to find the minimum values for a single record across a number of fields.

For example, lets say I have the following data:

Name Test1 Test2 Test3 LowScore
Adam 30 20 25
Fred 45 15 35
Julie 40 25 30

I can use a Totals query to show the minimum score for Test1, Test2 or Test3. What I need to do is show Adam's lowest score or Fred's lowest score regardless of which Test it was in for the LowScore field.

It sounds like a simple MIN formula but I can't get it to work. I want to be able to say MIN(Test1,Test2,Test3) but it is just not happening for me.

Any ideas?

Thanks
Chris

 

[Lightning]

How about a nested IIF statement

IIF(Test2 < Test1,IIF(Test3 < Test2, Test3,Test2),IIF(Test3 < Test1, Test3,Test1)

HTH
Lightning

 

[Lightning]

Oops!

You will probably want to test for Less than or Equal to in that statement

 

[chrisaroundtown]

In my real life situation I actually have 12 different fields to find the minimum of. The IIF function will get quite long for that many fields. Any ideas on how itcan be shorter?

Thanks
Chris

 

[amorous]

You mean something like this:

SELECT Name, Test1,Test2,Test3, MIN(Test1,Test2,Test3) AS LowScore FROM TableName Group By Name


Hope that helps.


VJ

 

[MichaelRed]

search Tek-Tips for 'basMinVal'. it can at least save a few keystriolkes and counting the parens and commas.

alternatively -and a better long term soloution- would be to review the normalization rules. the structure of your data is somewhat suggestive of a 'spreadsheet' approach to the design.



MichaelRed
mlred@verizon.net

 

[r937]

tsar, that's not how MIN() works, sorry

chris, go with MichaelRed's suggestion, and redesign the table

rudy

http://r937.com/

SQL Consulting