Solved

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

Posted on 2007-04-10
6
296 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now