Solved

Calculate min value from multiple fields for one record in Access 2003

Posted on 2007-04-10
6
326 Views
Last Modified: 2012-06-27
Hi,
I thought this would be an easy task, but need to ask for help.  I need to calculate minimum value of multiple fields for one record (one record has multiple contract terms and I need to calculate the minimum in a query in order to report on it).  In Excel I would just use =MIN(field1, field1, field3), but apparently the MIN function in Access/SQL finds the minimum of multiple records within one field.
How do I compare fields within one record to find the minimum in Access?
Thanks!
0
Comment
Question by:chrisandberto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18884621
Hello chrisandberto,

Try adding this function to your project:



Function GetMin(ParamArray args()) As Double

    Dim x As Variant
   
    For Each x In args
        If IsNumeric(x) Then
            If x < GetMin Then GetMin = x
        End If
    Next
   
End Function


Then use it like this:

SELECT *, GetMin(field1, field2, field3, field4, field5) AS TheMinimum
FROM SomeTable

Regards,

matthewspatrick
0
 

Author Comment

by:chrisandberto
ID: 18884785
Thank you so much for the prompt suggestion.  I added the function and tried the query, but I am getting all zeros, even when all fields have a number.  (And I tried changing the fields size to integer or double...it was byte).  Another thing to mention...often multiples of these fields will be null.

Other suggestions?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 18884917
chrisandberto,

This fixes the problem, and skips nulls.  If all the fields are null, it returns 0.

Function GetMin(ParamArray args()) As Double

    Dim x As Variant
    Dim First As Boolean
   
    First = True
   
    For Each x In args
        If IsNumeric(x) Then
            If First Then
                GetMin = x
                First = False
            Else
                If x < GetMin Then GetMin = x
            End If
        End If
    Next
       
End Function

matthewspatrick
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 75
ID: 18885315
Question:
How many fields are there ... and are they all the same data type?

mx
0
 

Author Comment

by:chrisandberto
ID: 18885336
That did it...thank you!!!

MX, there are 4 fields, and they are all number data types.
0
 
LVL 75
ID: 18885464
fwiw ... I was going to suggest a Union query ... union those 4 fields into one .... then, all you would need would be one more query to Group ... and do a Min on that combined field.  No code.  Oh well ...

mx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question