Minimum value from 4 fields 4

[sedgely]

i have a table that contains 4 numeric fields(among many others)what i want to do is create a query that will tell me, for each record, which of the 4 fileds contains the smallest (minimum) value and return that value.
i'm sure this is pretty simple but i can't figure it at the mo!

Cheers
Craig

 

[jrbarnett]

To obtain the minimum value try this:

Select Min (Fieldname) From Table
Where Fieldname in (Select field1, field2, field3, field4 from table where key = value)

John

 

[sedgely]

John
Thanx for such a prompt reply, will go away and try that now

Craig

 

[cmmrfrds]

Here is an example of a query that does a max on 4 different fields in a record.

SELECT Max(ut.fld) AS MaxOffld, ut.ID
FROM testtable AS A INNER JOIN [SELECT TestTable.ID, TestTable.field1 as fld
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field2
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field3
FROM TestTable
Union
SELECT TestTable.ID,TestTable.field4
FROM TestTable
]. AS ut ON A.ID = ut.ID
GROUP BY ut.ID;

 

[MichaelRed]

jrbarnett,

While I would MUCH prefer your approach, I do not quite see how to actually "do it". I do not quite see what 'FieldName' actually relates to. For a (silly) example, I have a table os stock qoutes ([tblDailyQuo]) with the fields [MinQuo], [OpnQuo], [ClsQuo], & [AvgPrice].

So, I take your example:

Select Min (Fieldname) From Table
Where Fieldname in (Select field1, field2, field3, field4 from table where key = value)

and attempt to modify it to suit:

Select Min (Fieldname) From Table
"Where Fieldname in (Select MinQuo, OpnQou, ClsQup, AvgPrice from tblDailyQuo;"

BUT (the UBIQUITOUS BUTT?) I have NO concept of what goes in place of FieldName.

In the mean time, I did resurect the two following small functions (in amongst a few "RowSet" aggregates I needed a while ago.

Public Function basMaxVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MAXIMUM or a series of values

    Dim Idx As Integer
    Dim MyMax As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (varMyVals(Idx) > MyMax) Then
            MyMax = varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basMaxVal = MyMax

End Function

[


code]
Public Function basMinVal(ParamArray varMyVals() As Variant) As Variant

'Michael Red 10/25/2001
'To return the MINIMUM or a series of values

'Sample Usage:
'? basMinVal(1, 5, 9, 3, 13.663)
'1

'?basMinVal(9, 1, 5, 3, 13.663)
'1

Dim Idx As Integer
Dim MyMin As Variant

If (UBound(varMyVals) < 0) Then
Exit Function
Else
MyMin = varMyVals(0)
End If

For Idx = 0 To UBound(varMyVals())
If (varMyVals(Idx) < MyMin) Then
MyMin = varMyVals(Idx)
End If
Next Idx

basMinVal = MyMin

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sedgely]

MichaelRed
Don't know if you're still watching this thread, i have a question for you with regard to your "minimum" function...
I have implemented the function using field names as arguments as follows:

Public Function basMinVal(ParamArray varMyVals() As Variant) As Variant

[COLOR=#ff0000][b]    'i have used:
    'basMinVal([field1], [field2], [field3],[field4])
    [/b][/color]
    Dim Idx As Integer
    Dim MyMin As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
     Else
        MyMin = varMyVals(0)
    End If

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) < MyMin) Then
            MyMin = varMyVals(Idx)
        End If
    Next Idx

    basMinVal = MyMin

End Function
The problem i have is that if the first field is null/empty the function will return null/empty. What i would like it to do is ignore fields that do not have a value and check the remaining fields and return the minimum from them.
Does this make sense?


Cheers, Craig
Si fractum non sit, noli id reficere

 

[Golom]

Here's my MinVal Function that should cover it.
[blue][tt]
Public Function MinVal(ParamArray Vals() As Variant) As Variant
Dim x As Variant
Dim MV As Variant
If UBound(Vals) = -1 Then Exit Function
MV = Vals(0)
For Each x In Vals
If Not IsNull(x) Then
If IsNull(MV) Then
MV = x
ElseIf x < MV Then
MV = x
End If
End If
Next
MinVal = MV
End Function
[/tt][/blue]
You need to do it this way because of the way a comparison like "If MV = X" is evaluated when one of the values is NULL.

 

[sedgely]

Thanks Golom
That was just what was needed

Cheers, Craig
Si fractum non sit, noli id reficere

 

[MichaelRed]

Public Function basMinVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red    4/22/04 _
     modified to account for Nulls.

    '? basMinVal(2, 3, Null, 4, 1, -1)

    Dim Idx As Integer
    Dim MyMin As Variant
    Dim MyMax As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
    End If

    While Idx <= UBound(varMyVals())

        If (Nz(varMyVals(Idx)) > MyMax) Then
            MyMax = varMyVals(Idx)
        End If

MaxNull:
        Idx = Idx + 1
    Wend

    MyMin = MyMax

    Idx = 0
    While Idx <= UBound(varMyVals())

        If (IsNull(varMyVals(Idx))) Then
            GoTo NullVal
        End If

        If (varMyVals(Idx) < MyMin) Then
            MyMin = varMyVals(Idx)
        End If

NullVal:
        Idx = Idx + 1
    Wend

    basMinVal = MyMin

End Function

MichaelRed
mlred@verizon.net

 

[sedgely]

Thanks MichaelRed
That also works.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[RobPotts]

Thanks guys

This has helped loads


Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[RobPotts]

Hi,

I now have a couple of other problems.

1) When I try to run a query on this calculated field (>30) it ask for the parameters to be entered. I therefore tried making a second query based on the first and running the criteria on it. I'd idearly like to only have the one query
2) When I run the criteria I get a type 13 mismatch error. The criteria is displayed >"30". I then created a table from the query and say that the calculated field was a text field, is there a way to make the field a number field so I can get the query to run.

Thanks in advance

Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[PHV]

Take a look at the Cxxx functions: CDbl, CSng, CDec, CLng, CInt, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244