How do I get the minimum from multiple calculated fields

Question

I have 5 calculated fields in an Access query and I need to determine the minimum number of all 5 fields.  I'm sure I could do a giant IIF statement but it seems there could be a better way.  I am calculating the difference between production and production levels.  If the person has already hit the highest level, the number should be 0 (zero). I am not a programmer so can anybody help me with this?

Answer 1

You have confirmed our suspicions.  In a relational database each level should not be represented by a column in a table, but by a row in a related table.  For each row in a promotions table there would therefore be up to five rows in a referencing table of levels whose columns would be a foreign key referencing the primary key of the promotions table, a column to identify the level, whose value could simply be a number within the range 1 to 5, and a third column to quantify the value achieved at that level.  The primary key of the referencing table would be a composite one made up of the first two of these columns.  It is then possible to compute any aggregations over the set of levels reached per promotion by use of the standard SQL aggregation operators like SUM, COUNT, MIN, MAX or AVG.

Answer 2

You can have multiple fields showing minimum in each separately.

SELECT SomeField, Min(AnotherField) AS MinOfAnotherField, Min(NextField) AS MinOfNextField, Min(LastField) AS MinOfLastField

FROM SomeTable

GROUP BY SomeField;

Answer 3

Perfect, happy you solved your issue!

Ciao

Ugo

Answer 4

Thank you but MIN handles only one expression, I have multiple.     

I found the MinOfList VBA code which works great.

Answer 5

Ciao,

I'm not sure I got your issue but did you try the MIN function in the expression builder?

Please let us know.

Thanks

Ugo