Enable the "Min" function in Access to compare 3 different fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Unknown1256 - 06 Dec 2005 17:30 GMT

I have four columns in my database; 1, 2, and 3 contain numbers. Column 4 is
where the smallest number out of the other three should go.

I need Access to compare three fields (1, 2, 3) and return the smallest
value to me in a separate field (4). The catch is, I need it to return the
value even if two out of three fields share the same, smallest value. For
example: field#1 = -1, field#2 = -3, field#3=-3. Access should return the
value "-3." I can do this in Excel but "Min" does not appear to be able to do
this in Microsoft Access 2003.

Reply to this Message

Duane Hookom - 06 Dec 2005 18:58 GMT

1) it seems your table structure might be un-normalized since you are
calculating across fields rather than across records.
2) do you have a good reason to store a calculated value?

Signature

Duane Hookom
MS Access MVP

>I have four columns in my database; 1, 2, and 3 contain numbers. Column 4
>is
[quoted text clipped - 17 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=01431efe-665d-4
898-b2d7-c8afd4d08a0e&dg=microsoft.public.access.queries

Reply to this Message

John Spencer - 06 Dec 2005 19:04 GMT

Assuming no null values in your fields:

Field: IIF(Field1<Field2 and Field1<Field3,
Field1,IIF(Field2<Field3,Field2,Field3))

If you have more than three values to compare this gets very complex
quickly. You would need a custom function then.

If your fields can be null, then you will need to wrap each of the fields
above in the NZ function and set the nulls to some value that is known to be
larger than any value contained in your table.

Reply to this Message