Sort/find minimum value in row entries

[RobG]

I would like a query that will tell me the minimum non-zero value in a
row.

Say I have a table with a column called recordID that contains unique
record IDs, and have a set of values named V1, V2 up to V9 that I want
to find the minimum value for each record. The values are decimal
floating point numbers, some of the columns contain zeros but I want to
ignore those and get the minimum non-zero number.

I know how to do column sorting, but doing it for the values in a row
has me stumped.

Can someone provide a starting point?
--
Rob

[Allen Browne]

Access does not do this, because ultimately it's a bad data design.

You can write your own function to do this. The example below chooses the
largest from any number of items passed in. Easy enough to modify it to give
you the smallest.

Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RobG" <rg***@iinet.net.auau> wrote in message
news:IL*****************@news.optus.net.au...

I would like a query that will tell me the minimum non-zero value in a
row.

Say I have a table with a column called recordID that contains unique
record IDs, and have a set of values named V1, V2 up to V9 that I want
to find the minimum value for each record. The values are decimal
floating point numbers, some of the columns contain zeros but I want to
ignore those and get the minimum non-zero number.

I know how to do column sorting, but doing it for the values in a row
has me stumped.

Can someone provide a starting point?
--
Rob

[RobG]

Allen Browne wrote:

Access does not do this, because ultimately it's a bad data design.
Access does not implement any functionality that might be useful for
solving issues of 'bad data design'? Altruism knows no bounds! ;-)

You can write your own function to do this. The example below chooses the
largest from any number of items passed in. Easy enough to modify it to give
you the smallest.

[...]

Thanks, your help is greatly appreciated.
--
Rob

[Bas Cost Budde]

RobG wrote:

I know how to do column sorting, but doing it for the values in a row
has me stumped.

On my site, under Code Modules, there is an explanation on field sorting.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea