Find the lowest value in a row

[tm5]

I am trying to find the lowest value per row. My data looks like this

Number cost1 cost2 cost3 cost 4
152 50 25 30 20
200 15 20 18 25
300 22 19 32 12

I need for my results to show in a new column, which should give me
20
15
12

I tried the DMin function but,it didn't work.
DMin([Cost1],DMin([Cost2],DMin([Cost3],DMin([Cost4],"tablename"). Please
Help!

 

[Wolfgang Kais]

tm5.

I am trying to find the lowest value per row. My data looks like
this:
Number cost1 cost2 cost3 cost 4
152 50 25 30 20
200 15 20 18 25
300 22 19 32 12

I need for my results to show in a new column, which should give me
20
15
12

I tried the DMin function but,it didn't work.
DMin([Cost1],DMin([Cost2],DMin([Cost3],DMin([Cost4],"tablename").
Please

There is no standard function you can use, you will have to write
your own (in a standard module):

Function MinimumValue(ParamArray Values())
Dim MinVal as Variant, i as Integer
If UBound(Values) >= 0 Then
MinVal = Values(0)
For i = 1 To UBound(Values)
If Values(i) < MinValue Then MinValue = Values(i)
Next
Else
MinValue = Null
End If
MinimumValue = MinValue
End Function

 

[John Spencer]

DMin works down rows, not across columns.

Your table should really look more like
Number
CostColumnNumber
Cost

Then the query is simple. You can either use a custom VBA function or you
can use a normalizing union query to fix your data.
A union query cannot be built using the design view, but must be built using
the SQL window.

SELECT Number, Cost1 as Cost
FROM YourTable
UNION ALL
SELECT Number, Cost2 as Cost
FROM YourTable
UNION ALL
SELECT Number, Cost3 as Cost
FROM YourTable
UNION ALL
SELECT Number, Cost4 as Cost
FROM YourTable

Save that query and then you can use it as the source for a totals query.

Using a custom vba function - one posted below. Copy it and paste it into a
VBA module and save the module - with a name other than fRowMin.

In your query, instead of DMin, use fRowMin

Field: LowestCost: fRowMin(Cost1,Cost2,Cost3,Cost4)


'================ Code follows =================
Public Function fRowMin(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the minimum Number of a group of values passed to it.
'Sample call: myMin = GetMinNumber("-21","TEST","2","3",4,5,6,"7",0)
' returns -21
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to nest fRowMin for groups of fields.

Dim i As Integer, vMin As Variant
Dim tfFound As Boolean, dblCompare As Double

vMin = 1E+308 'very large positive number
For i = LBound(Values) To UBound(Values)

If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare < vMin Then
vMin = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fRowMin = vMin
Else
fRowMin = Null
End If

End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..