MS Access question: Finding the minimum value in multiple fields

Jadis · January 19, 2005, 2:14pm

What expression can I write that will look at Fields A, B and C and return the lowest of those 3 values into Field D (using an update query)?

I’ve tried to use the Min function as Min(Field A, Field B, Field C), but it’s telling me “The expression you entered has a function containing the wrong number of arguments”. I have no idea what that means. Is Min not the expression that I should be using?

I suspect you can do it using a complicated If statement, but I’m too braindead to figure out how to write it in such a way as to reliably return the lowest of 3 values. Seems like there should be an easier way.

TIA

Athena · January 19, 2005, 2:37pm

You were close.

The Min function needs a range. So if your values are in cells A1 through A3, the correct syntax is Min(A1:A3)

Jadis · January 19, 2005, 2:41pm

I’m trying to use the Min function in Access, though…not Excel. Is there something in Access that works a similar way?

zev_steinhardt · January 19, 2005, 2:55pm

You could try using a UNION query.
SELECT field1 FROM table
UNION
SELECT field2 FROM table
UNION
SELECT field3 FROM table

and order the results in ascending order. The low number will come out on top.

Zev Steinhardt

Joe_Random · January 19, 2005, 3:01pm

Min will probably do the trick, but it only takes two arguments, not three. What should work is Min(Min(Field A, Field B),Field C). It’s ugly, and I’m sure there’s a much more elegant way to do what you want, but I rarely use Access, and when I do, I always have a frontend using something like Visual Basic, so I can just grab all the values with a query, programatically find the smallest, and then insert that into a generated update statement.

Mangetout · January 19, 2005, 3:06pm

As you’re using an update query to put the lowest value into field D, you could just do it with three queries instead, so

First:

UPDATE Table1 SET Table1.FieldD = [FieldA];

then

UPDATE Table1 SET Table1.FieldD = [FieldB] WHERE (((Table1.FieldD)>[FieldB]));

then

UPDATE Table1 SET Table1.FieldD = [FieldC] WHERE (((Table1.FieldD)>[FieldC]));

At each stage, FieldD will only be updated if the value being compared is lower than what is already in there

There’s got to be a better way though - one that does it in a single operation, but I’m scratching my head…

Jadis · January 19, 2005, 3:27pm

Mangetout, I wound up doing something similar…I created temp fields and did a few queries to compare A to B and B to C, then the results of those 2 resulting fields to each other. Seems to have worked, but yeah…it’s ugly, and it seems like there should be a better way.

I didn’t try JoeRandom’s suggestion about nesting the Min statements, but I might try it after I’m done with this project just for future reference.

Mangetout · January 19, 2005, 3:34pm

There is a way of doing it with nested IIF (Immediate If) functions, but it isn’t going to be pretty; I’ll post back in a minute when I’ve tested it

Mangetout · January 19, 2005, 3:52pm

OK, take a deep breath, sit down…


UPDATE Table1 SET Table1.FieldD = IIf(((IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc]);

It works, honest! Here’s how:

Iif(<Expression>,<truepart>,<falsepart>) returns truepart if the expression is true and falsepart if it is false, so…

Iif([fieldA]<[FieldB],[fieldA],[fieldB]) returns the value of whichever out of A or B is lower - let’s call this little nugget of code X

Now we need another evaluation to check whether X is lower than FieldC and return the FieldC value or the X value, whichever is lower, so…
Iif(X<[fieldC],X,[fieldC]) - except that’s pseudocode and doesn’t work, so we replace X (both instances) with what it really is - the evaluation of FieldA and FieldB and we get:
IIf(((IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc])

Or maybe it’s clearer this way:

IIf((([COLOR=Blue]IIf([fieldA]<[fieldB],[fieldA],[fieldB])))<[fieldc],((IIf([fieldA]<[fieldB],[fieldA],[fieldB]))),[fieldc])[/COLOR]

Then again, maybe not. It works.

Jadis · January 19, 2005, 4:04pm

Heh…you’re way more patient than I am. And yes, I think I follow what the logic in the code is, but I’ll be damned if I had the brainpower today to work it out.

Thanks!

Tabby_Cat · January 19, 2005, 5:28pm

Why doesn’t min(A,(min(B,C)) work? Looks fine to me, and far less complicated than that monstrosity Mangetout came up with… no doubt his solution would work, but why doesn’t nested min work?

Athena · January 19, 2005, 6:52pm

Good Lord. Someone really needs to revoke my posting rights pre-coffee. Sorry for the mixup.

kpinocchio · January 19, 2005, 10:59pm

The Min function in an Access query only accepts one argument which is meant to be a column name. It is designed to find the lowest value in the column passed as the only argument.

You might be thinking of the Min function in Excel.

chrisk · January 19, 2005, 11:22pm

I have to say that in my opinion, the absence of a ‘scalar’, non-aggregating minimum function (and maximum function) is one of the most annoying shortcomings in access query language. I don’t think it would take much to come up with an alternate function name for it, and there are so many places where I would glady have used it rather than come up with complicated IIF constructions. (You could probably even set it up to take arbitrarily many arguments if you were feeling ambitious.)

::sighs::

ultrafilter · January 19, 2005, 11:37pm

Yeah…

On the other hand, you could argue that if your table is set up in such a way that such a comparison is meaningful, there’s probably an issue with your table design.

K364 · January 20, 2005, 4:16am

You can define your own functions quite easily. To solve your problem:

create a new module, call it “UDF”
paste the following:



Public Function fMin1(arg1, arg2, Optional arg3) As Variant
    Dim vMin As Variant
    
    If arg1 < arg2 Then
        vMin = arg2
    Else
        vMin = arg1
    End If
    If Not IsMissing(arg3) Then
        If arg3 < vMin Then
            vMin = arg3
        End If
    End If
    fMin1 = vMin
    
End Function

Public Function fMin2(ParamArray varValues() As Variant) As Variant

    Dim I As Integer, vMin As Variant
    vMin = varValues(0)
    For I = 1 To UBound(varValues())
        If IsNull(vMin) Then
            vMin = varValues(I)
        ElseIf varValues(I) < vMin Then
            vMin = varValues(I)
        End If
    Next I
    fMin2 = vMin

End Function

(I have given you two functions. fMin2 uses a “ParamArray” - I’m not sure when it was introduced into MS Access, so I also gave you fMin1 which will work in older versions. The advantage of fMin2 is that any number of values (including 1!) can be passed, whereas fMin1 requires either 2 or 3 values passed.)

So, next use your new User-Defined Function(s) in an expression:
Expr2: fmin2([field1],[field2],[field3])… returns the minimum of the three fields.

Note that fMin2 can handle nulls “properly” (they are ignored), fMin1 will need some work to handle nulls, I leave that up to you!

K364 · January 20, 2005, 4:19am

Of course, I screwed it up somehow :smack:

The first function should be:


Public Function fMin1(arg1, arg2, Optional arg3) As Variant
    Dim vMin As Variant
    
    If arg1 < arg2 Then
        vMin = arg1
    Else
        vMin = arg2
    End If
    If Not IsMissing(arg3) Then
        If arg3 < vMin Then
            vMin = arg3
        End If
    End If
    fMin1 = vMin
    
End Function

cmkeller · January 20, 2005, 4:25am

If Access language follows the conventions of SQL, then the MIN function is to find the lowest value in a database column, and what you’d use to get the lowest value in a list of values is LEAST.

zoid · January 20, 2005, 5:15am

Someone has to say it so I’ll do it…
Your data is not normalized. Your tables need to be redesigned and your data reformatted for a relational database.

Sorry for the hijack, carry on.

ccwaterback · January 20, 2005, 8:25am

Please elaborate.