How to find out Minimum of numbers in rows of a table

[mukesh]

Thanks a lot

I will try your suggestions after a while but there is one another question -

I have a table having fields as - num1, num2, num3 and num4

I have a query named "MinimumNum"

Now in the calculated field of query "MinimumNum" - how can i get the minimum of num1, num2 num3, num4

please help me - i used - Minmum: DMin( num1, num2, num3, num4) - but it does not work.

please help me

thanks in advance

[PEB]

Hi,

Use select query and press the sigma bouton for SUm!

So in the group by fill Min under every numeric field!

:)

[mukesh]

Hi,

Use select query and press the sigma bouton for SUm!

So in the group by fill Min under every numeric field!

:)

Thanks for your answer and support - but I need a formula for calculated field in a query which automatically selects minimum number amongst the numbers in more than one "row" (not a column) of a table

please help.

[PEB]

So for you I've developped this function:

You introduce like a SQL parameter a query on which you would like to find the minimum between columns, also introduce the start column with numeric data to be compared and the end column! Between the columns you have to have only numeric fields that have to be compared!

The function returns only the first line! So in your SQL supply only one line!

:)

Function min_in_columns(SQL, Start_col, End_col) As Double
Dim mydb As Database
Dim myr As Recordset
Dim i
Dim min_value
 
Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQL)
 
min_value = 0
 
myr.MoveFirst
For i = Start_col To End_col
    If min_value > myr(i) Then
        min_value = myr(i)
    End If
Next i
myr.Close
mydb.Close
 
min_in_columns = min_value
End Function
 

[mukesh]

So for you I've developped this function:

You introduce like a SQL parameter a query on which you would like to find the minimum between columns, also introduce the start column with numeric data to be compared and the end column! Between the columns you have to have only numeric fields that have to be compared!

The function returns only the first line! So in your SQL supply only one line!

:)

Function min_in_columns(SQL, Start_col, End_col) As Double
Dim mydb As Database
Dim myr As Recordset
Dim i
Dim min_value
 
Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQL)
 
min_value = 0
 
myr.MoveFirst
For i = Start_col To End_col
    If min_value > myr(i) Then
        min_value = myr(i)
    End If
Next i
myr.Close
mydb.Close
 
min_in_columns = min_value
End Function
 

Is this a module? How shoud i use it

I have save it as 'module1' and in the calculated field of query table typed - Min: min_in_columns ([numer1], [number2], [number3])

but when i open the query, it opens the module automatically.

I do not know how to write module and how to refere the same in query to work it.

please help me. You are too intelegent to help me, i know it.

[PEB]

Hi,

You have done well that you saved this code in a module...

Before save it go in Debug or Run I don't remember more and choose the commande Compile module...

If there is no errors so the function is ok...

And then you need to use it in your query...

So imagine

Your table with numbers is named My_numbers and has the folloing columns:

ID - Your primary key of your table
Description - Text field that has no importance for our numbers
Numb1 - your first number
Numb2 - your Second number
.......
Numb32

And you need to see the minimum of your 32 fields isn't it?

So in your query
In the Field row type:

Wanted_Minimum:min_in_columns("SELECT * FROM My_numbers WHERE ID=" & STR([ID]), 2, 33)

NOTE: YOUR NUMBERS ARE ONE AFTER ONE IN YOUR SQL!!! THERE IS NO NOTHING EXCEPT THE WANTED NUMBERS THAT PARTICIPATE IN THE MINIMUM!

Have a nice day!

:)