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

[Guest]

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.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...afd4d08a0e&dg=microsoft.public.access.queries

 

[Duane Hookom]

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?

 

[John Spencer]

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.