Solved

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

Posted on 2007-04-10
6
288 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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.

758 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

18 Experts available now in Live!

Get 1:1 Help Now