Solved

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

Posted on 2007-04-10
6
306 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

809 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